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 persistence; import datastructure.OrderListData; import datastructure.OrderSearchData; import entity.Admin; import entity.Author; import entity.AuthorReject; import entity.Order; import entity.Payment; import entity.orderStatus.OrderStatus; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import mvc.formObjects.ReportFormData; import org.apache.log4j.Logger; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.criterion.DetachedCriteria; import org.hibernate.criterion.MatchMode; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Property; import org.hibernate.criterion.Restrictions; import org.hibernate.criterion.Subqueries; import org.hibernate.internal.util.SerializationHelper; import org.hibernate.transform.Transformers; import org.hibernate.type.BooleanType; import org.springframework.stereotype.Repository; import persistence.parent.Dao; import support.CollectionUtils; import support.DateUtils; import support.SqlUtils; import org.hibernate.type.IntegerType; import support.FormatDate; import support.StringAdapter; /** * * @author Rice Pavel */ @Repository("ordersDao") public class OrderDao extends Dao<Order> { protected Logger log = Logger.getLogger(this.getClass()); public String sql = ""; public Class getSupportedClass() { return Order.class; } public List<Order> getByEmails(List<String> emails) { if (emails != null && !emails.isEmpty()) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.add(Restrictions.in("clientEmail", emails)); return crit.list(); } else { return new ArrayList(); } } public int getCountAll(OrderStatus status) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.setProjection(Projections.rowCount()); if (status != null) { crit.add(Restrictions.eq("status", status)); } Long count = (Long) crit.uniqueResult(); return count.intValue(); } public List<Order> getAll(OrderStatus status, Integer count) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); if (status != null) { crit.add(Restrictions.eq("status", status)); } if (count != null) { crit.setMaxResults(count); } return crit.list(); } /** * * @param status * @return ? , ? * */ public int getCountOrdersWithFiles(OrderStatus status) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.setProjection(Projections.rowCount()); if (status != null) { crit.add(Restrictions.eq("status", status)); } crit.add(Restrictions.or(Restrictions.isNotEmpty("files"), Restrictions.isNotEmpty("readyFiles"))); Long count = (Long) crit.uniqueResult(); return count.intValue(); } /** * * @param status * @param count * @return , ? */ public List<Order> getOrdersWithFiles(OrderStatus status, Integer count) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); if (status != null) { crit.add(Restrictions.eq("status", status)); } if (count != null) { crit.setMaxResults(count); } crit.add(Restrictions.or(Restrictions.isNotEmpty("files"), Restrictions.isNotEmpty("readyFiles"))); return crit.list(); } public List<Order> getAll(OrderStatus status) { return getAll(status, null); } public List<Order> getOrders(Set<Long> brancheIds) { brancheIds.add(0L); Query query = currentSession().createQuery( "select ord from Order as ord join ord.branch where ord.branch.branchId in (:ids) order by ord.orderId desc"); if (!brancheIds.isEmpty()) { query.setParameterList("ids", brancheIds); } List<Order> list = query.list(); return list; } public List<Order> getDelegatedOrders(Long orderId) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.createAlias("parentOrder", "parentOrder"); crit.add(Restrictions.eq("parentOrder.orderId", orderId)); return crit.list(); } /** * ?? map. map: 'order; - ? * , 'otherOrders' - * * @param brancheIds * @return */ public List<OrderListData> getOrdersAsDataList(Set<Long> brancheIds) { List<Order> list = getOrders(brancheIds); return transformList(list); } public List<Order> getOrdersWithFilesReadyOrArchive(Date dateFrom, Date dateTo) { Criteria crit = getCriteriaDistinctRootEntity(getSupportedClass()); crit.add(Restrictions.between("deadlineDate", dateFrom, dateTo)); crit.add(Restrictions.or(Restrictions.eq("status", OrderStatus.READY), Restrictions.eq("status", OrderStatus.ARCHIVE))); crit.createCriteria("readyFiles"); return crit.list(); } public List<Order> getAssignedUnloadedInShop() { Criteria crit = getCriteriaDistinctRootEntity(getSupportedClass()); crit.add(Restrictions.eq("assignedUnloadedInShop", true)); crit.add(Restrictions.or(Restrictions.eq("unloadedInShop", false), Restrictions.isNull("unloadedInShop"))); return crit.list(); } public List<Order> searchFromCalendar(Date date, List<OrderStatus> statuces, Set<Long> rightBranchIds) { rightBranchIds.add(0L); if (!statuces.isEmpty()) { Date dateFrom = DateUtils.startOfDay(date); Date dateTo = DateUtils.endOfDay(date); Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.add(Restrictions.in("status", statuces)); crit.add(Restrictions.between("deadlineDate", dateFrom, dateTo)); crit.createAlias("branch", "branch"); crit.add(Restrictions.in("branch.branchId", rightBranchIds)); return crit.list(); } else { return new ArrayList(); } } /** * ?? ?? map * * @param dateFrom * @param dateTo * @param branchId * @return */ public List<Map> getStatistics(Date dateFrom, Date dateTo, Long branchId, Set<Long> branchIds) { String select = "select new map(ord as ord, sum(payment.amount) as amount_sum, ord.author_salary as author_salary, sum(payment.amount) - ord.author_salary as profit) " + " from Order as ord left join ord.payments as payment " + " where ord.readyDate >= :dateFrom and ord.readyDate <= :dateTo "; if (branchId != null) { select += " and ord.branch.branchId = :branchId "; } if (branchIds != null & !branchIds.isEmpty()) { select += " and ord.branch.branchId in :branchIdList "; } select += " group by ord order by ord.readyDate "; Query query = currentSession().createQuery(select); query.setDate("dateFrom", dateFrom); query.setDate("dateTo", dateTo); if (branchId != null) { query.setLong("branchId", branchId); } if (branchIds != null & !branchIds.isEmpty()) { query.setParameterList("branchIdList", branchIds); } return query.list(); } /** * ??, ? ??, ?? Map. * ? map: month, year, day, amount_sum, author_salary, profit * * @param dateFrom * @param dateTo * @param branchId * @return */ /* public List<Map> getStatisticsByDates(Date dateFrom, Date dateTo, Long branchId) { String select = " select new map(month(ord.readyDate) as month, year(ord.readyDate) as year, day(ord.readyDate) as day, sum(payment.amount) as amount_sum, sum(ord.author_salary) as author_salary, sum(payment.amount) - sum(ord.author_salary) as profit) from Order as ord left join ord.payments as payment where ord.readyDate >= :dateFrom and ord.readyDate <= :dateTo "; if (branchId != null) { select += " and ord.branch.branchId = :branchId "; } select += " group by month(ord.readyDate), year(ord.readyDate), day(ord.readyDate) order by year(ord.readyDate), month(ord.readyDate), day(ord.readyDate) "; Query query = currentSession().createQuery(select); query.setDate("dateFrom", dateFrom); query.setDate("dateTo", dateTo); if (branchId != null) { query.setLong("branchId", branchId); } return query.list(); } */ /** * ??, ? ??, ?? Map. * ? map: month, year, day, amount_sum, author_salary, profit * * @param dateFrom * @param dateTo * @param branchId * @return */ public List<Map> getStatisticsByDates(Date dateFrom, Date dateTo, Long branchId, Set<Long> branchIds) { String sql = " select month(t.readyDate) as month, year(t.readyDate) as year, day(t.readyDate) as day, t.amount_sum as amount_sum, " + " t.author_salary as author_salary, t.profit as profit " + " from ( " + " select o.order_id, o.ready_date as readyDate, sum(p.amount) as amount_sum, o.author_salary as author_salary, sum(p.amount) - o.author_salary as profit " + " from orders as o left join payment as p " + " on p.order_id = o.order_id " + " where o.ready_date >= :dateFrom and o.ready_date <= :dateTo "; if (branchId != null) { sql += " and o.branch_id = :branchId "; } if (branchIds != null & !branchIds.isEmpty()) { sql += " and o.branch_id in :branchIdList "; } sql += " group by o.order_id " + " ) t " + " group by month(t.readyDate), year(t.readyDate), day(t.readyDate) order by year(t.readyDate), month(t.readyDate), day(t.readyDate) "; SQLQuery query = (SQLQuery) currentSession().createSQLQuery(sql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); query.setDate("dateFrom", dateFrom); query.setDate("dateTo", dateTo); if (branchId != null) { query.setLong("branchId", branchId); } if (branchIds != null & !branchIds.isEmpty()) { query.setParameterList("branchIdList", branchIds); } return query.list(); } public List<Order> getNewOrdersWithoutPayments() { String select = "select ord from Order as ord left join ord.payments as payment where ord.status = :status and ord.cost is not null group by ord.orderId having sum(payment.amount) = 0 or sum(payment.amount) is null"; Query query = currentSession().createQuery(select); query.setParameter("status", OrderStatus.NEW); return query.list(); } public List<Order> lessTheDayBeforeDeadline() { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.add(Restrictions.isNotNull("author")); crit.add(Restrictions.eq("status", OrderStatus.WORKING)); crit.add(Restrictions.gt("deadlineDate", dayAgo())); return crit.list(); } private Date dayAgo() { Calendar cl = Calendar.getInstance(); cl.add(Calendar.DAY_OF_YEAR, -1); return cl.getTime(); } /** * * * @param dateFrom * @param dateTo * @return */ public List<Order> getOrders(Date dateFrom, Date dateTo) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.add(Restrictions.between("deadlineDate", dateFrom, dateTo)); return crit.list(); } /** * ? ?? - , email * * @param clientEmail - email * @return */ public List<Order> getWorkingByEmail(String clientEmail) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.add(Restrictions.eq("clientEmail", clientEmail)); crit.add(Restrictions.eq("status", OrderStatus.WORKING)); return crit.list(); } public List<Order> getByEmail(String clientEmail) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.add(Restrictions.eq("clientEmail", clientEmail)); return crit.list(); } public List<Order> getByEmail(String clientEmail, Order excludeOrder) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.add(Restrictions.eq("clientEmail", clientEmail)); crit.add(Restrictions.ne("orderId", excludeOrder.getOrderId())); return crit.list(); } @Override public void update(Order obj) { currentSession().merge(obj); } public List<Order> getOrders(Set<Long> rightBrancheIds, ReportFormData data) { OrderSearchData osd = new OrderSearchData(); osd.orderTypeIds = data.orderTypeIds; osd.directionIds = data.directionIds; osd.branchIds = data.brancheIds; osd.from = data.from; osd.to = data.to; return getOrders(rightBrancheIds, null, osd, null, null); } /** * ? ? , ? ?? - ?, ?? * * @param author - * @param authorDirectionIds - ? * @param branchIds - * @param status - ?? * @param searchData - ? ? * @param start - ?, ? ?? * @param numberOfRecords - ? ? * @return */ @Deprecated public List<Order> getForAuthorNotReject(Author author, Set<Long> authorDirectionIds, Set<Long> branchIds, OrderStatus status, OrderSearchData searchData, Integer start, Integer numberOfRecords) { authorDirectionIds.add(0L); branchIds.add(0L); DetachedCriteria subCrit = DetachedCriteria.forClass(AuthorReject.class, "ar"); subCrit.setProjection(Projections.property("authorRejectId")); subCrit.add(Restrictions.eq("ar.author", author)); subCrit.add(Restrictions.eqProperty("ar.order.orderId", "ord.orderId")); Set<Long> resultBranchIds = resultBranchIds(branchIds, searchData); Set<Long> resultDirectionIds = resultDirectionIds(authorDirectionIds, searchData); Criteria crit = currentSession().createCriteria(Order.class, "ord"); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); crit.createAlias("ord.branch", "branch"); crit.add(Restrictions.in("branch.branchId", resultBranchIds)); crit.createAlias("ord.directions", "dirs"); crit.add(Restrictions.in("dirs.directionId", resultDirectionIds)); if (status != null) { crit.add(Restrictions.eq("ord.status", status)); } addConditionToAuthorSearch(crit, searchData); crit.add(Restrictions.or(Restrictions.eq("ord.author", author), Restrictions.and(Restrictions.isNull("ord.author"), Restrictions.eq("ord.status", OrderStatus.NEW), Subqueries.notExists(subCrit)))); List<Order> list = crit.list(); list = CollectionUtils.limit(list, start, numberOfRecords); return list; } public List<Order> getOverdueForAdmin(Set<Long> branchIds, Integer start, Integer numberOfRecords) { branchIds.add(0L); Criteria crit = currentSession().createCriteria(Order.class); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); crit.createAlias("branch", "branch"); crit.add(Restrictions.in("branch.branchId", branchIds)); addOverdueCondition(crit); List<Order> list = crit.list(); list = CollectionUtils.limit(list, start, numberOfRecords); return list; } public Long getOverdueCountForAdmin(Set<Long> branchIds) { branchIds.add(0L); Criteria crit = currentSession().createCriteria(Order.class); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); crit.setProjection(Projections.countDistinct("orderId")); crit.createAlias("branch", "branch"); crit.add(Restrictions.in("branch.branchId", branchIds)); addOverdueCondition(crit); return (Long) crit.uniqueResult(); } private void addOverdueCondition(Criteria crit) { Date now = new Date(); Date threeDaysAgo = getThreeDaysAgo(); crit.add(Restrictions.or( Restrictions.and(Restrictions.lt("deadlineDate", now), Restrictions.eq("status", OrderStatus.WORKING)), Restrictions.and(Restrictions.isNotNull("reworkDate"), Restrictions.lt("reworkDate", threeDaysAgo)))); crit.add(Restrictions.in("status", new OrderStatus[] { OrderStatus.WORKING, OrderStatus.REWORK })); } private Date getThreeDaysAgo() { Calendar cl = Calendar.getInstance(); cl.add(Calendar.DAY_OF_YEAR, -3); return cl.getTime(); } public List<Order> getOverdueForAuthor(Set<Long> branchIds, Author author, Integer start, Integer numberOfRecords) { branchIds.add(0L); Criteria crit = currentSession().createCriteria(Order.class); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); crit.createAlias("branch", "branch"); crit.add(Restrictions.eq("author", author)); crit.add(Restrictions.in("branch.branchId", branchIds)); addOverdueCondition(crit); List<Order> list = crit.list(); list = CollectionUtils.limit(list, start, numberOfRecords); return list; } public Long getOverdueCountForAuthor(Set<Long> branchIds, Author author) { branchIds.add(0l); Criteria crit = currentSession().createCriteria(Order.class); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); crit.setProjection(Projections.countDistinct("orderId")); crit.createAlias("branch", "branch"); crit.add(Restrictions.eq("author", author)); crit.add(Restrictions.in("branch.branchId", branchIds)); addOverdueCondition(crit); return (Long) crit.uniqueResult(); } private Set<Long> resultBranchIds(Set<Long> branchIds, OrderSearchData searchData) { Set<Long> resultBranchIds; if (searchData.branchIds != null && !searchData.branchIds.isEmpty()) { resultBranchIds = CollectionUtils.crossing(branchIds, searchData.branchIds); } else { resultBranchIds = branchIds; } return resultBranchIds; } private Set<Long> resultDirectionIds(Set<Long> directionIdsIds, OrderSearchData searchData) { Set<Long> resultDirectionIds; if (searchData.directionIds != null && !searchData.directionIds.isEmpty()) { resultDirectionIds = CollectionUtils.crossing(directionIdsIds, searchData.directionIds); } else { resultDirectionIds = directionIdsIds; } return resultDirectionIds; } @Deprecated public List<Order> getForAuthorReject(Author author, Set<Long> authorDirectionIds, Set<Long> branchIds, OrderSearchData searchData, Integer start, Integer numberOfRecords) { DetachedCriteria subCrit = DetachedCriteria.forClass(AuthorReject.class, "ar"); subCrit.setProjection(Projections.property("authorRejectId")); subCrit.add(Restrictions.eq("ar.author", author)); subCrit.add(Restrictions.eqProperty("ar.order.orderId", "ord.orderId")); Set<Long> resultBranchIds = resultBranchIds(branchIds, searchData); Set<Long> resultDirectionIds = resultDirectionIds(authorDirectionIds, searchData); Criteria crit = currentSession().createCriteria(Order.class, "ord"); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); crit.createAlias("ord.branch", "branch"); resultBranchIds.add(0L); crit.add(Restrictions.in("branch.branchId", resultBranchIds)); crit.createAlias("ord.directions", "dirs"); resultDirectionIds.add(0L); crit.add(Restrictions.in("dirs.directionId", resultDirectionIds)); addConditionToAuthorSearch(crit, searchData); crit.add(Restrictions.and(Restrictions.isNull("ord.author"), Restrictions.eq("ord.status", OrderStatus.NEW), Subqueries.exists(subCrit))); List<Order> list = crit.list(); list = CollectionUtils.limit(list, start, numberOfRecords); return list; } private void addConditionToAuthorSearch(Criteria crit, OrderSearchData searchData) { if (searchData.orderTypeIds != null && !searchData.orderTypeIds.isEmpty()) { crit.createAlias("orderType", "ot"); crit.add(Restrictions.in("ot.orderTypeId", searchData.orderTypeIds)); } if (searchData.from != null) { crit.add(Restrictions.gt("ord.orderDate", searchData.from)); } if (searchData.to != null) { crit.add(Restrictions.le("ord.orderDate", searchData.to)); } if (searchData.authorId != null) { crit.createAlias("author", "author"); crit.add(Restrictions.eq("author.userId", searchData.authorId)); } } public List<Order> reportForUnloadInShop(Date dateFrom, Date dateTo) { Criteria crit = getCriteriaDistinctRootEntity(getSupportedClass()); crit.add(Restrictions.ge("readyDate", dateFrom)); crit.add(Restrictions.le("readyDate", dateTo)); return crit.list(); } /** * ? ? ? , ? ?? * * @param branchIds * @param authorForSearch , ?. * null. * @param status ??. null * @param branchId . null * @return ?? ??. ? ??: [1] => status, [2] => * count */ public List<Object[]> getCountsForAdmin(Set<Long> branchIds, Author authorForSearch, OrderStatus status, Long branchId, List<OrderStatus> rightStatusList) { Criteria crit = currentSession().createCriteria(Order.class); crit.setResultTransformer(Criteria.PROJECTION); crit.createAlias("branch", "branch"); branchIds.add(0L); crit.add(Restrictions.in("branch.branchId", branchIds)); if (authorForSearch != null) { crit.add(Restrictions.eq("author", authorForSearch)); } if (status != null && !status.equals(OrderStatus.PRICED)) { crit.add(Restrictions.eq("status", status)); } if (status != null && status.equals(OrderStatus.PRICED)) { crit.add( Restrictions.or( Restrictions.and(Restrictions.eq("status", OrderStatus.NEW), Restrictions.isNotNull("cost"), Restrictions.ne("cost", 0d), Restrictions.or(Restrictions.eq("statusOfUser", false), Restrictions.isNull("statusOfUser"))), Restrictions.eq("status", status))); } crit.add(Restrictions.in("status", rightStatusList)); if (branchId != null) { crit.add(Restrictions.eq("branch.branchId", branchId)); } if (status == null) { crit.setProjection(Projections.projectionList().add(Property.forName("status")) .add(Projections.alias(Projections.count("orderId"), "count")) .add(Projections.groupProperty("status"))); } else { crit.setProjection(Projections.projectionList().add(Property.forName("status")) .add(Projections.alias(Projections.count("orderId"), "count"))); } return crit.list(); } private List<Order> getOrders(Set<Long> rightBrancheIds, OrderStatus status, OrderSearchData searchData, Integer start, Integer numberOfRecords) { // ? removeNill(searchData.branchIds, searchData.orderTypeIds, searchData.directionIds); if (!rightBrancheIds.isEmpty()) { Criteria crit = currentSession().createCriteria(Order.class); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); crit.createAlias("branch", "b"); crit.add(Restrictions.in("b.branchId", rightBrancheIds)); addConditions(crit, status, searchData); crit.addOrder(org.hibernate.criterion.Order.asc("orderDate")); List<Order> list = crit.list(); list = CollectionUtils.limit(list, start, numberOfRecords); return list; } else { return new ArrayList(); } } /** * ?? * * @param rightBrancheIds , ? ? * @param status ??, ?? ? * @param searchData * @param start * @param numberOfRecords * @return */ @Deprecated public List<Order> getForAdmin(Set<Long> rightBrancheIds, OrderStatus status, OrderSearchData searchData, Integer start, Integer numberOfRecords) { // ? removeNill(searchData.branchIds, searchData.orderTypeIds, searchData.directionIds); if (!rightBrancheIds.isEmpty()) { Criteria crit = currentSession().createCriteria(Order.class); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); crit.createAlias("branch", "b"); crit.add(Restrictions.in("b.branchId", rightBrancheIds)); addConditions(crit, status, searchData); crit.addOrder(org.hibernate.criterion.Order.desc("orderId")); List<Order> list = crit.list(); list = CollectionUtils.limit(list, start, numberOfRecords); return list; } else { return new ArrayList(); } } public Long getCountForAuthor(Author author, Set<Long> authorDirectionIds, Set<Long> branchIds, OrderStatus status, List<OrderStatus> rightStatusList, Boolean overdue) { List<Map> map = _getForAuthorBySql(author, status, authorDirectionIds, branchIds, new OrderSearchData(), null, null, null, true, rightStatusList, overdue); try { return Long.valueOf(map.get(0).get("count").toString()); } catch (Exception e) { return 0L; } } /** * ? * * @param author , ? * @param status ?? . null * @param authorDirectionIds ? * @param branchIds , ? ? * @param searchData ? ? * @param start ? ? ? * @param numberOfRecords ? ? * @param statusList ?? ?? * @return */ public List<Map> getForAuthorBySql(Author author, OrderStatus status, Set<Long> authorDirectionIds, Set<Long> branchIds, OrderSearchData searchData, Integer start, Integer numberOfRecords, List<OrderStatus> statusList, List<OrderStatus> rightStatusList, Boolean overdue) { return _getForAuthorBySql(author, status, authorDirectionIds, branchIds, searchData, start, numberOfRecords, statusList, false, rightStatusList, overdue); } private List<Map> _getForAuthorBySql(Author author, OrderStatus status, Set<Long> authorDirectionIds, Set<Long> branchIds, OrderSearchData searchData, Integer start, Integer numberOfRecords, List<OrderStatus> statusList, boolean count, List<OrderStatus> rightStatusList, Boolean overdue) { branchIds.add(0L); authorDirectionIds.add(0L); String selectCountMess = getMessageSelectForAuthor(); List<String> statusStringList = getStringList(statusList); List<String> rightStatusStringList = getStringList(rightStatusList); String select; if (count) { select = "select count(distinct o.order_id) as count "; } else { select = " select distinct o.order_id, " + " o.subject, o.order_date, " + " o.deadline_date, o.real_date, o.client_fio, " + " o.client_phone, o.client_email, o.city, " + " o.cost, o.author_salary, o.first_flag, " + " o.second_flag, o.status, o.branch_id, o.order_type_id, o.author_id, " + " o.comment as comment, o.author_comment as author_comment , o.ready_date as ready_date, o.comment_to_author_salary as comment_to_author_salary, " + " o.unloaded_in_shop as unloaded_in_shop, o.parent_order_id as parent_order_id, o.selected, o.child_selected, o.status_of_user, o.old_id, " + selectCountMess + " , " + getSelectPaymentSum() + ", " + " if( o.status = :statusNew and o.cost is not null and o.cost != 0 and (status_of_user is null or status_of_user = false ), 0, 1) newCost "; } StringBuffer from = new StringBuffer(" from orders as o " + " inner join branch as b "); from.append(" left join order_to_direction otd " + " on otd.order_id = o.order_id "); String subSelectReject = " select * " + " from author_rejection as ar " + " where ar.author_id = :authorId " + " and ar.order_id = o.order_id "; StringBuffer where = null; if (overdue == true) { where = new StringBuffer(" where " + " o.branch_id = b.branch_id and " + " ((o.deadline_date <= :overduedate and o.status=:overduestatus) or " + " (o.rework_date <= :threedaysdate and o.status=:reworkstatus)) " + " and (b.branch_id in (:rightBranchIds) or o.author_id = :authorId) "); } else { where = new StringBuffer(" where " + " o.branch_id = b.branch_id and " + " (b.branch_id in (:rightBranchIds) or o.author_id = :authorId) "); } // , ? ( ) where.append(" and (o.child_selected is null or o.child_selected = false or o.child_selected = 0 ) "); where.append(" and o.status in :rightStatusList "); String whereReject = " ( " + " (o.author_id is null) and " + " o.status = 'NEW' and exists(" + subSelectReject + ") " + " ) "; String whereNotReject = " ( " + " ( (o.author_id is null and o.status = 'NEW') or ( o.author_id = :authorId ) ) " + " and not exists(" + subSelectReject + ") " + " ) "; if (status == null) { where.append(" and ").append(whereNotReject); } else if (status.equals(OrderStatus.REJECTION)) { where.append(" and ").append(whereReject); } else { where.append(" and ").append(whereNotReject); } if (status != null && !status.equals(OrderStatus.REJECTION) && !status.equals(OrderStatus.PRICED)) { where.append(" and o.status = :status "); } if (status != null && status.equals(OrderStatus.PRICED)) { where.append( " and (o.status = :status or (o.status = 'NEW' and o.cost is not null and o.cost != 0 and (status_of_user is null or status_of_user = false ) ) )"); } where.append(" and o.status != 'REJECTION' "); if (statusStringList != null && !statusStringList.isEmpty()) { where.append(" and o.status in (:statusList) "); } where.append(" and (o.author_id = :authorId or otd.direction_id in (:authorDirections) ) "); String order = ""; if (!count) { order = " order by count_mess desc, newCost, o.order_id desc "; } StringBuffer limit = new StringBuffer(""); addConditionsForAuthor(where, from, limit, searchData, start, numberOfRecords); addConditionForNotActive(author, where); String innerSql = select + from + where + order + limit; String resultSql; if (count) { resultSql = innerSql; } else { resultSql = getResultSql(innerSql); } SQLQuery query = (SQLQuery) currentSession().createSQLQuery(resultSql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); if (overdue == true) { query.setParameter("overduedate", FormatDate.getDateInMysql(FormatDate.getStartOfDate(new Date()))); query.setParameter("overduestatus", OrderStatus.WORKING.toString()); query.setParameter("threedaysdate", FormatDate.getDateInMysql(getThreeDaysAgo())); query.setParameter("reworkstatus", OrderStatus.REWORK.toString()); } query.setParameterList("rightBranchIds", branchIds); query.setParameter("authorId", author.getId()); query.setParameterList("authorDirections", authorDirectionIds); query.setParameterList("rightStatusList", rightStatusStringList); if (status != null && !status.equals(OrderStatus.REJECTION)) { query.setParameter("status", status.toString()); } setParamsForNotActive(author, query); if (statusStringList != null && !statusStringList.isEmpty()) { query.setParameterList("statusList", statusStringList); } if (!count) { query.setParameter("statusNew", OrderStatus.NEW.toString()); } setParamsForAuthor(query, searchData, getStringForLike(searchData.searchString)); sql = resultSql; return (List<Map>) query.list(); } private void addConditionForNotActive(Author author, StringBuffer where) { if ((author.getActive() == null || author.getActive() == false) && author.getCancelActiveDate() != null) { where.append(" and o.order_date < :closeDate "); } } private void setParamsForNotActive(Author author, SQLQuery query) { if ((author.getActive() == null || author.getActive() == false) && author.getCancelActiveDate() != null) { Date closeDate = author.getCancelActiveDate(); query.setParameter("closeDate", closeDate); } } private String getMessageSelectForAuthor() { return " (select count(mes.message_id) " + " from message mes " + " inner join user us " + " where " + " mes.user_id = us.user_id and " + " (mes.ready is null or mes.ready = false) " + " and us.userType = 'admin' " + " and mes.order_id = o.order_id " + " and mes.author_destination_id is not null " + " and mes.author_destination_id = :authorId " + " ) as count_mess, " + countAllAuthorMessagesForAuthor(); } private void addConditionsForAuthor(StringBuffer where, StringBuffer from, StringBuffer limit, OrderSearchData searchData, Integer start, Integer numberOfRecords) { if (searchData.deadlineFrom != null) { where.append(" and (o.deadline_date >= :deadlineFrom) "); } if (searchData.deadlineTo != null) { where.append(" and (o.deadline_date <= :deadlineTo) "); } if (searchData.realDateFrom != null) { where.append(" and (o.real_date >= :realDateFrom) "); } if (searchData.realDateTo != null) { where.append(" and (o.real_date <= :realDateTo) "); } if (searchData.orderTypeIds != null && !searchData.orderTypeIds.isEmpty()) { where.append(" and o.order_type_id in (:orderTypes) "); } if (searchData.authorId != null) { where.append(" and o.author_id = :author_id"); } if (searchData.branchIds != null && !searchData.branchIds.isEmpty()) { where.append(" and o.branch_id in (:branchIds)"); } if (searchData.searchString != null && !searchData.searchString.isEmpty()) { where.append( " and (o.order_id = :searchId or subject like :searchStr or client_fio like :searchStr or client_phone like :searchStr or client_email like :searchStr or old_id like :searchStr )"); } if (start != null && numberOfRecords != null) { limit.append(" limit ").append(start).append(", ").append(numberOfRecords); } } private void setParamsForAuthor(SQLQuery query, OrderSearchData searchData, String like) { if (searchData.deadlineFrom != null) { query.setDate("deadlineFrom", searchData.deadlineFrom); } if (searchData.deadlineTo != null) { query.setParameter("deadlineTo", searchData.deadlineTo); } if (searchData.realDateFrom != null) { query.setDate("realDateFrom", searchData.realDateFrom); } if (searchData.realDateTo != null) { query.setParameter("realDateTo", searchData.realDateTo); } if (searchData.authorId != null) { query.setParameter("author_id", searchData.authorId); } if (searchData.orderTypeIds != null && !searchData.orderTypeIds.isEmpty()) { query.setParameterList("orderTypes", searchData.orderTypeIds); } if (searchData.branchIds != null && !searchData.branchIds.isEmpty()) { query.setParameterList("branchIds", searchData.branchIds); } if (searchData.searchString != null && !searchData.searchString.isEmpty()) { query.setParameter("searchId", searchData.searchString); } if (like != null && !like.isEmpty()) { query.setParameter("searchStr", like); } } private void addConditionsForAdmin(StringBuffer where, StringBuffer from, StringBuffer limit, List<String> statusStringList, OrderSearchData searchData, Integer start, Integer numberOfRecords) { if (statusStringList != null && !statusStringList.isEmpty()) { where.append(" and (o.status in (:statusList)) "); } if (searchData.deadlineFrom != null) { where.append(" and (o.deadline_date >= :deadlineFrom) "); } if (searchData.deadlineTo != null) { where.append(" and (o.deadline_date <= :deadlineTo) "); } if (searchData.realDateFrom != null) { where.append(" and (o.real_date >= :realDateFrom) "); } if (searchData.realDateTo != null) { where.append(" and (o.real_date <= :realDateTo) "); } if (searchData.orderTypeIds != null && !searchData.orderTypeIds.isEmpty()) { where.append(" and o.order_type_id in (:orderTypes) "); } if (searchData.directionIds != null && !searchData.directionIds.isEmpty()) { from.append(" inner join order_to_direction otd "); where.append(" and otd.order_id = o.order_id " + " and otd.direction_id in (:directions) "); } if (searchData.authorId != null) { where.append(" and o.author_id = :author_id"); } if (searchData.branchIds != null && !searchData.branchIds.isEmpty()) { where.append(" and o.branch_id in (:branchIds)"); } if (searchData.searchString != null && !searchData.searchString.isEmpty()) { where.append(" and (o.order_id = :searchId " + " or subject like :searchStr " + " or client_fio like :searchStr " + " or client_phone like :searchStr " + " or client_email like :searchStr " + " or old_id like :searchStr ) "); } if (start != null && numberOfRecords != null) { limit.append(" limit ").append(start).append(", ").append(numberOfRecords); } } private void setParamsForAdmin(SQLQuery query, List<String> statusStringList, OrderSearchData searchData, String like) { if (statusStringList != null && !statusStringList.isEmpty()) { query.setParameterList("statusList", statusStringList); } if (searchData.deadlineFrom != null) { query.setDate("deadlineFrom", searchData.deadlineFrom); } if (searchData.deadlineTo != null) { query.setParameter("deadlineTo", searchData.deadlineTo); } if (searchData.realDateFrom != null) { query.setDate("realDateFrom", searchData.realDateFrom); } if (searchData.realDateTo != null) { query.setParameter("realDateTo", searchData.realDateTo); } if (searchData.authorId != null) { query.setParameter("author_id", searchData.authorId); } if (searchData.orderTypeIds != null && !searchData.orderTypeIds.isEmpty()) { query.setParameterList("orderTypes", searchData.orderTypeIds); } if (searchData.directionIds != null && !searchData.directionIds.isEmpty()) { query.setParameterList("directions", searchData.directionIds); } if (searchData.branchIds != null && !searchData.branchIds.isEmpty()) { query.setParameterList("branchIds", searchData.branchIds); } if (searchData.searchString != null && !searchData.searchString.isEmpty()) { query.setParameter("searchId", searchData.searchString); } if (like != null && !like.isEmpty()) { query.setParameter("searchStr", like); } } /** * ? * * @param rightBranchIds , ? * @param status ?? * @param statusList ?? ?? * @param searchData ? ? * @param adminId * @param start ?, ? * @param numberOfRecords ? ? * @param rightStatusList * @return */ public List<Map> getForAdminBySql(Set<Long> rightBranchIds, OrderStatus status, List<OrderStatus> statusList, OrderSearchData searchData, Long adminId, Integer start, Integer numberOfRecords, List<OrderStatus> rightStatusList, Boolean overdue) { long starttime = System.currentTimeMillis(); rightBranchIds.add(0L); String branchStr = setToString(rightBranchIds); List<String> statusStringList = getStringList(statusList); List<String> rightStatusStringList = getStringList(rightStatusList); String select = " select distinct o.order_id, " + " o.subject, o.order_date, " + " o.deadline_date, o.real_date, o.client_fio, " + " o.client_phone, o.client_email, o.city, " + " o.cost, o.author_salary, o.first_flag, " + " o.second_flag, o.status, o.branch_id, o.order_type_id, o.author_id, " + " o.comment as comment, o.author_comment as author_comment , o.ready_date as ready_date, o.comment_to_author_salary as comment_to_author_salary, " + " o.unloaded_in_shop as unloaded_in_shop, o.parent_order_id as parent_order_id, o.selected, o.child_selected, o.status_of_user, o.old_id, " + " (select count(mes.message_id) " + " from message mes " + " inner join user us " + " where " + " mes.user_id = us.user_id "; select += " and mes.messageType = 'authorMessage' " + " and (mes.ready is null or mes.ready = false) " + " and mes.message_text is not null and mes.message_text not like '' " + " and us.userType = 'author' " + " and mes.order_id = o.order_id " + " ) as count_mess, " + getSelectPaymentSum() + ", " + countAllAuthorMessagesForAdmin() + ", " + countAllAdminMessages() + ", " + countNotReadyAdminMessages() + ", " + countDelegateMessages() + ", " + countNotReadyDelegateMessages() + ", " + " if( o.status = :statusNew and o.cost is not null and o.cost != 0 and (status_of_user is null or status_of_user = false ), 0, 1) newCost "; StringBuffer from = new StringBuffer(" from orders as o " + " "); StringBuffer where = null; if (overdue == true) { where = new StringBuffer(" where " + " " + " o.branch_id in (" + branchStr + ") and " + " (o.status=:overduestatus or o.status=:reworkstatus) and ((o.deadline_date < :overduedate) or (" + "o.rework_date is not null and o.rework_date < :threedaysdate ))"); } else { where = new StringBuffer(" where " + " " + " o.branch_id in (" + branchStr + ") "); } if (status != null && !status.equals(OrderStatus.PRICED)) { where.append(" and o.status = :status "); } if (status != null && status.equals(OrderStatus.PRICED)) { where.append( " and (o.status = :status or (o.status = 'NEW' and o.cost is not null and o.cost != 0 and (status_of_user is null or status_of_user = false )) )"); } where.append(" and o.status in :rightStatusList "); String order = " order by o.order_id desc, client_phone, subject "; if (status == null) { order = " order by count_mess desc, count_admin_mess_not_ready desc, delegate_mess_not_ready desc, newCost, o.order_id desc, client_phone, subject "; } StringBuffer limit = new StringBuffer(""); addConditionsForAdmin(where, from, limit, statusStringList, searchData, start, numberOfRecords); String innerSql = select + from + where + order + limit; String resultSql = getResultSql(innerSql); SQLQuery query = (SQLQuery) currentSession().createSQLQuery(resultSql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); setParamsForAdmin(query, statusStringList, searchData, getStringForLike(searchData.searchString)); query.setParameter("adminId", adminId); if (overdue == true) { query.setParameter("overduedate", FormatDate.getDateInMysql(FormatDate.getStartOfDate(new Date()))); query.setParameter("overduestatus", OrderStatus.WORKING.toString()); query.setParameter("threedaysdate", FormatDate.getDateInMysql(getThreeDaysAgo())); query.setParameter("reworkstatus", OrderStatus.REWORK.toString()); } query.setParameterList("rightStatusList", rightStatusStringList); if (status != null) { query.setParameter("status", status.toString()); } query.setParameter("statusNew", OrderStatus.NEW.toString()); //log.warn("preSql "+Long.valueOf(System.currentTimeMillis()-starttime)); List<Map> lsm = (List<Map>) query.list(); //log.warn("postSql "+Long.valueOf(System.currentTimeMillis()-starttime)); return lsm; } /*public List<Object[]> getTestOrdersAndCountsForAdminBySql( Set<Long> rightBranchIds, OrderStatus status, OrderSearchData searchData, Long adminId, Integer start, Integer numberOfRecords, List<OrderStatus> rightStatusList, Boolean overdue ) { long starttime= System.currentTimeMillis(); rightBranchIds.add(0L); String branchStr = setToString(rightBranchIds); List<String> rightStatusStringList = getStringList(rightStatusList); String select = " select o.order_id, " + " o.deadline_date, o.real_date, o.client_fio, " + " o.client_phone, o.client_email, o.city, " + " o.cost, o.author_salary, o.first_flag, " + " o.second_flag, o.status, o.branch_id, o.order_type_id, o.author_id, " + " o.comment as comment, o.author_comment as author_comment , o.ready_date as ready_date, o.comment_to_author_salary as comment_to_author_salary, " + " o.unloaded_in_shop as unloaded_in_shop, o.parent_order_id as parent_order_id, o.selected, o.child_selected, o.status_of_user, o.old_id, o.subject, " + " o.order_date, br.name as branch_name, br.abbrevation as abbrevation, ot.name as order_type_name, " + " u.login as author_login, u.name as author_name, u.surname as author_surname, " + " (select count(mes.message_id) " + " from message mes " + " inner join user us " + " where " + " mes.user_id = us.user_id "; select += " and mes.messageType = 'authorMessage' " + " and (mes.ready is null or mes.ready = false) " + " and mes.message_text is not null and mes.message_text not like '' " + " and us.userType = 'author' " + " and mes.order_id = o.order_id " + " ) as count_mess, " + getSelectPaymentSum() + ", " + countAllAuthorMessagesForAdmin() + ", " + countAllAdminMessages() + ", " + countNotReadyAdminMessages() + ", " + countDelegateMessages() + ", " + countNotReadyDelegateMessages() + ", " + " if( o.status = :statusNew and o.cost is not null and o.cost != 0 and (status_of_user is null or status_of_user = false ), 0, 1) newCost, " + " if((select count(asal.author_salary_id) from author_salary asal where asal.order_id=o.order_id and o.status=:statusNew)>0 and (o.cost is null or o.cost = 0), 0, 1) as blackOrder "; StringBuffer from = new StringBuffer(" from orders as o left join branch as br on o.branch_id=br.branch_id " + " left join order_type as ot on o.order_type_id=ot.order_type_id " + " left join user as u on o.author_id=u.user_id "); StringBuffer where = null; if (overdue == true) { where = new StringBuffer(" where " + " " + " o.branch_id in (" + branchStr + ") and " + " (o.status=:overduestatus or o.status=:reworkstatus) and ((o.deadline_date < :overduedate) or (" + "o.rework_date is not null and o.rework_date < :threedaysdate ))"); } else { where = new StringBuffer(" where " + " " + " o.branch_id in (" + branchStr + ") "); } if (status != null && !status.equals(OrderStatus.PRICED)) { where.append(" and o.status = :status "); } if (status != null && status.equals(OrderStatus.PRICED)) { where.append(" and (o.status = :status or (o.status = 'NEW' and o.cost is not null and o.cost != 0 and (status_of_user is null or status_of_user = false )) )"); } where.append(" and o.status in :rightStatusList "); String order = " order by o.order_id desc, client_phone, subject "; if (status == null) { order = " order by blackOrder, count_admin_mess_not_ready desc, count_mess desc, delegate_mess_not_ready desc, newCost, o.order_id desc, client_phone, subject "; } StringBuffer limit = new StringBuffer(""); addConditionsForAdmin(where, from, limit, null, searchData, start, numberOfRecords); String sqlString = select + from + where + order + limit; SQLQuery query = (SQLQuery) currentSession().createSQLQuery(sqlString); setParamsForAdmin(query, null, searchData, getStringForLike(searchData.searchString)); if (overdue == true) { query.setParameter("overduedate", FormatDate.getDateInMysql(FormatDate.getStartOfDate(new Date()))); query.setParameter("overduestatus", OrderStatus.WORKING.toString()); query.setParameter("threedaysdate", FormatDate.getDateInMysql(getThreeDaysAgo())); query.setParameter("reworkstatus", OrderStatus.REWORK.toString()); } query.setParameterList("rightStatusList", rightStatusStringList); query.setParameter("adminId", adminId); query.setParameter("statusNew", OrderStatus.NEW.name()); if (status != null) { query.setParameter("status", status.toString()); } log.warn("preSql1 "+Long.valueOf(System.currentTimeMillis()-starttime)); List<Object[]>res = query.list(); log.warn("postSql1 "+Long.valueOf(System.currentTimeMillis()-starttime)); return res; }*/ public List<Object[]> getTestOrdersAndCountsForAdminBySql(Set<Long> rightBranchIds, OrderStatus status, OrderSearchData searchData, Long adminId, Integer start, Integer numberOfRecords, List<OrderStatus> rightStatusList, Boolean overdue) { long starttime = System.currentTimeMillis(); rightBranchIds.add(0L); String branchStr = setToString(rightBranchIds); List<String> rightStatusStringList = getStringList(rightStatusList); String select = " select o.order_id, " + " o.deadline_date, o.real_date, o.client_fio, " + " o.client_phone, o.client_email, o.city, " + " o.cost, o.author_salary, o.first_flag, " + " o.second_flag, o.status, o.branch_id, o.order_type_id, o.author_id, " + " o.comment as comment, o.author_comment as author_comment , o.ready_date as ready_date, o.comment_to_author_salary as comment_to_author_salary, " + " o.unloaded_in_shop as unloaded_in_shop, o.parent_order_id as parent_order_id, o.selected, o.child_selected, o.status_of_user, o.old_id, o.subject, " + " o.order_date, br.name as branch_name, br.abbrevation as abbrevation, ot.name as order_type_name, " + " u.login as author_login, u.name as author_name, u.surname as author_surname, " + " (select count(mes.message_id) " + " from message mes " + " inner join user us " + " where " + " mes.user_id = us.user_id " + " and mes.messageType = 'authorMessage' " + " and (mes.ready is null or mes.ready = false) " + " and mes.message_text is not null and mes.message_text not like '' " + " and us.userType = 'author' " + " and mes.order_id = o.order_id " + " ) as count_mess, " + getSelectPaymentSum() + ", " + countAllAuthorMessagesForAdmin() + ", " + countAllAdminMessages() + ", " + countNotReadyAdminMessages() + ", " + countDelegateMessages() + ", " + countNotReadyDelegateMessages() + ", " /*+ " if(v.order_view_id is null,0,1) watched, "*/ + " if( o.status = :statusNew and o.cost is not null and o.cost != 0 and (status_of_user is null or status_of_user = false ), 0, 1) newCost, " + " (CASE WHEN (select count(mes.message_id) from message mes inner join user us where mes.user_id = us.user_id and mes.messageType = 'authorMessage' and (mes.ready is null or mes.ready = false) and mes.message_text is not null and mes.message_text not like '' and us.userType = 'author' and mes.order_id = o.order_id)>0 and o.status != :statusNew THEN 0 WHEN (select count(mes.message_id) from message mes inner join user us where mes.user_id = us.user_id and mes.messageType = 'authorMessage' and (mes.ready is null or mes.ready = false) and mes.message_text is not null and mes.message_text not like '' and us.userType = 'author' and mes.order_id = o.order_id)>0 and o.status = :statusNew THEN 1 ELSE 2 END) as newMessageAndStatus," //+ " (CASE WHEN (select count(mes.message_id) from message mes where mes.messageType = 'authorMessage' and (mes.ready is null or mes.ready = false) and mes.message_text is not null and mes.message_text not like '' and mes.order_id = o.order_id)>0 and o.status != :statusNew THEN 0 WHEN (select count(mes.message_id) from message mes where mes.messageType = 'authorMessage' and (mes.ready is null or mes.ready = false) and mes.message_text is not null and mes.message_text not like '' and mes.order_id = o.order_id)>0 and o.status = :statusNew THEN 1 ELSE 2 END) as newMessageAndStatus," //+ " (case when (select count(mes.message_id) from message mes where mes.messageType = 'authorMessage' and (mes.ready is null or mes.ready = false) and mes.message_text is not null and mes.message_text not like '' and mes.order_id = o.order_id) as mes_count>0 and o.status != :statusNew then 0 when mes_count>0 and o.status = :statusNew then 1 when mes_count=0 then 2) as newMessageAndStatus," //+ " if( o.status != :statusNew and (select count(mes.message_id) from message mes where mes.messageType = 'authorMessage' and (mes.ready is null or mes.ready = false) and mes.message_text is not null and mes.message_text not like '' and mes.order_id = o.order_id)>0, 0, 1) as newAuthMessNotNewStatus, " //+ " if( o.status != :statusNew and count_mess!=0, 0, 1) as newAuthMessNotNewStatus "; + " if((select count(asal.author_salary_id) from author_salary asal where asal.order_id=o.order_id and o.status=:statusNew)>0 and (o.cost is null or o.cost = 0), 0, 1) as blackOrder "; StringBuffer from = new StringBuffer(" from orders as o left join branch as br on o.branch_id=br.branch_id " + " left join order_type as ot on o.order_type_id=ot.order_type_id " + " left join user as u on o.author_id=u.user_id " /*+ " left join order_view as v on o.order_id=v.order_id and v.user_id=:adminId"*/); StringBuffer where = null; if (overdue == true) { where = new StringBuffer(" where " + " " + " o.branch_id in (" + branchStr + ") and " + " (o.status=:overduestatus or o.status=:reworkstatus) and ((o.deadline_date < :overduedate) or (" + "o.rework_date is not null and o.rework_date < :threedaysdate ))"); } else { where = new StringBuffer(" where " + " " + " o.branch_id in (" + branchStr + ") "); } if (status != null && !status.equals(OrderStatus.PRICED)) { where.append(" and o.status = :status "); } if (status != null && status.equals(OrderStatus.PRICED)) { where.append( " and (o.status = :status or (o.status = 'NEW' and o.cost is not null and o.cost != 0 and (status_of_user is null or status_of_user = false )) )"); } where.append(" and o.status in :rightStatusList "); String order = " order by o.order_id desc, client_phone, subject "; if (status == null) { order = " order by blackOrder, count_admin_mess_not_ready desc, newMessageAndStatus, count_mess desc, delegate_mess_not_ready desc, newCost, o.order_id desc, client_phone, subject "; } StringBuffer limit = new StringBuffer(""); addConditionsForAdmin(where, from, limit, null, searchData, start, numberOfRecords); String sqlString = select + from + where + order + limit; SQLQuery query = (SQLQuery) currentSession().createSQLQuery(sqlString); setParamsForAdmin(query, null, searchData, getStringForLike(searchData.searchString)); if (overdue == true) { query.setParameter("overduedate", FormatDate.getDateInMysql(FormatDate.getStartOfDate(new Date()))); query.setParameter("overduestatus", OrderStatus.WORKING.toString()); query.setParameter("threedaysdate", FormatDate.getDateInMysql(getThreeDaysAgo())); query.setParameter("reworkstatus", OrderStatus.REWORK.toString()); } query.setParameterList("rightStatusList", rightStatusStringList); query.setParameter("adminId", adminId); query.setParameter("statusNew", OrderStatus.NEW.name()); if (status != null) { query.setParameter("status", status.toString()); } //log.warn("preSql1 "+Long.valueOf(System.currentTimeMillis()-starttime)); List<Object[]> res = query.list(); /*String fstRes = ""; for(Object o:res.get(0)){ fstRes+=StringAdapter.getString(o)+"; "; }*/ //log.warn("resCount: "+res.size()); //log.warn("text: "+query.getQueryString()); //log.warn("postSql1 "+Long.valueOf(System.currentTimeMillis()-starttime)); return res; } public List<Object[]> getTestOtherOrdersForAdminBySql(Set<Long> orderIds) { if (orderIds == null || orderIds.isEmpty()) { return new ArrayList(); } else { String select = "select o.order_id as order_id, other_o.order_id as other_order_id, other_o.branch_id as other_order_branch_id, other_o.old_id as other_old_id"; String from = " from orders as o inner join orders as other_o on o.client_email = other_o.client_email and other_o.client_email is not null " + "and other_o.client_email != '' and other_o.order_id != o.order_id "; String where = " where o.order_id in (:orderIds)"; String sqlString = select + from + where; SQLQuery query = (SQLQuery) currentSession().createSQLQuery(sqlString); query.setParameterList("orderIds", orderIds); List<Object[]> res = query.list(); return res; } } public List<Object[]> getTestDirectionsForAdminBySql(Set<Long> orderIds) { if (orderIds == null || orderIds.isEmpty()) { return new ArrayList(); } else { long starttime = System.currentTimeMillis(); String select = "select o.order_id as order_id, d.direction_id as direction_id, d.name as direction_name"; String from = " from order_to_direction as o left join direction as d on o.direction_id=d.direction_id"; String where = " where o.order_id in (:orderIds) "; String sqlString = select + from + where; SQLQuery query = (SQLQuery) currentSession().createSQLQuery(sqlString); query.setParameterList("orderIds", orderIds); List<Object[]> res = query.list(); return res; } } public List<Object[]> getTestSalariesForAdminBySql(Set<Long> orderIds) { if (orderIds == null || orderIds.isEmpty()) { return new ArrayList(); } else { long starttime = System.currentTimeMillis(); String select = "select order_id, cost as author_salary_cost, author_salary_id "; String from = " from author_salary "; String where = " where order_id in (:orderIds) "; String sqlString = select + from + where; SQLQuery query = (SQLQuery) currentSession().createSQLQuery(sqlString); query.setParameterList("orderIds", orderIds); List<Object[]> res = query.list(); return res; } } private String getStringForLike(String searchString) { if (searchString == null || searchString.isEmpty()) { return searchString; } else { String[] arr = searchString.split(" "); String like = "%"; for (String s : arr) { like += s + "%"; } return like; } } private String getSelectPaymentSum() { return " (select sum(p.amount) from payment as p where p.order_id = o.order_id) as payment_sum "; } private String countAllAuthorMessagesForAdmin() { return "( select count(*) " + " from message as m inner join user as u " + " where m.order_id = o.order_id and m.user_id = u.user_id " + " and m.message_text is not null and m.message_text not like '' " + " and u.userType = 'author' and m.messageType = 'authorMessage' ) as count_author_mess_all "; } private String countAllAuthorMessagesForAuthor() { return "( select count(*) " + " from message as m inner join user as u " + " where m.order_id = o.order_id and m.user_id = u.user_id " + " and u.userType = 'admin' and m.messageType = 'authorMessage' and m.author_destination_id = :authorId ) as count_author_mess_all "; } private String countAllAdminMessages() { return " (select count(*) from message as m where m.order_id = o.order_id and m.messageType = 'adminMessage' and m.user_id != :adminId) as count_admin_mess_all "; } private String countNotReadyAdminMessages() { return " (select count(*) from message as m where m.order_id = o.order_id and m.messageType = 'adminMessage' and (m.ready is null or m.ready = false) and m.user_id != :adminId) count_admin_mess_not_ready "; } private String countDelegateMessages() { return " (" + " select count(*) " + " from DelegateMessage as m inner join orders as ord_from " + " where " + " m.order_from_id = ord_from.order_id " + " and m.order_to_id = o.order_id " + " and m.insert_user_id != :adminId " + " and ord_from.parent_order_id = o.order_id " + " ) " + " as delegate_mess_all "; } private String countNotReadyDelegateMessages() { return " (" + " select count(*) " + " from DelegateMessage as m inner join orders as ord_from " + " where " + " m.order_from_id = ord_from.order_id " + " and m.order_to_id = o.order_id " + " and m.insert_user_id != :adminId " + " and ord_from.parent_order_id = o.order_id " + " and (m.ready is null or m.ready = false) " + " ) " + " as delegate_mess_not_ready "; } private List<String> getStringList(List<OrderStatus> statusList) { List<String> statusStrList = new ArrayList(); if (statusList != null) { for (OrderStatus st : statusList) { if (st != null) { statusStrList.add(st.toString()); } } } return statusStrList; } private String getResultSql(String subSelect) { String sql = " select o.*, " + " other_o.order_id as other_order_id, other_o.branch_id as other_order_branch_id, other_o.old_id as other_order_old_id, " + " b.name as branch_name, b.abbrevation as abbrevation, " + " d.direction_id as direction_id, d.name as direction_name, " + " ot.name as order_type_name, " + " u.login as author_login, u.name as author_name, u.surname as author_surname, " + " sal.cost as author_salary_cost, sal.author_salary_id as author_salary_id " + " from (" + subSelect + ") as o " + " left join orders as other_o " + " on " + " o.client_email = other_o.client_email " + " and other_o.client_email is not null " + " and other_o.client_email != '' " + " and other_o.order_id != o.order_id " + " left join branch as b " + " on o.branch_id = b.branch_id " + " left join order_to_direction otd " + " on o.order_id = otd.order_id " + " left join direction as d " + " on otd.direction_id = d.direction_id " + " left join order_type as ot " + " on o.order_type_id = ot.order_type_id " + " left join user as u " + " on o.author_id = u.user_id " + " left join author_salary as sal " + " on o.order_id = sal.order_id "; return sql; } private String setToString(Set<Long> set) { String str = ""; int n = 1; for (Long l : set) { str += l; if (n < set.size()) { str += ", "; } n++; } return str; } public Long countOverdueForAdmin() { Date currentDate = new Date(); Criteria crit = currentSession().createCriteria(Order.class); crit.setProjection(Projections.rowCount()); crit.add(Restrictions.and(Restrictions.eq("status", OrderStatus.WORKING), Restrictions.gt("realDate", currentDate))); return (Long) crit.uniqueResult(); } private void addConditions(Criteria crit, OrderStatus status, OrderSearchData searchData) { if (searchData.orderTypeIds != null && !searchData.orderTypeIds.isEmpty()) { crit.createAlias("orderType", "ot"); crit.add(Restrictions.in("ot.orderTypeId", searchData.orderTypeIds)); } if (searchData.directionIds != null && !searchData.directionIds.isEmpty()) { crit.createAlias("directions", "dir"); crit.add(Restrictions.in("dir.directionId", searchData.directionIds)); } if (searchData.branchIds != null && !searchData.branchIds.isEmpty()) { crit.add(Restrictions.in("b.branchId", searchData.branchIds)); } if (searchData.from != null) { crit.add(Restrictions.gt("orderDate", searchData.from)); } if (searchData.to != null) { crit.add(Restrictions.le("orderDate", searchData.to)); } if (status != null) { crit.add(Restrictions.eq("status", status)); } if (searchData.authorId != null) { crit.createAlias("author", "author"); crit.add(Restrictions.eq("author.userId", searchData.authorId)); } // ? ? ? if (searchData.searchString != null && !searchData.searchString.isEmpty()) { Long searchNumber = 0L; try { searchNumber = Long.valueOf(searchData.searchString); } catch (Exception e) { } if (searchNumber != 0) { crit.add(Restrictions.or(Restrictions.eq("orderId", searchNumber), Restrictions.like("clientFio", searchData.searchString, MatchMode.ANYWHERE), Restrictions.like("subject", searchData.searchString, MatchMode.ANYWHERE), Restrictions.like("clientPhone", searchData.searchString, MatchMode.ANYWHERE), Restrictions.like("clientEmail", searchData.searchString, MatchMode.ANYWHERE))); } else { crit.add( Restrictions.or(Restrictions.like("clientFio", searchData.searchString, MatchMode.ANYWHERE), Restrictions.like("subject", searchData.searchString, MatchMode.ANYWHERE), Restrictions.like("clientPhone", searchData.searchString, MatchMode.ANYWHERE), Restrictions.like("clientEmail", searchData.searchString, MatchMode.ANYWHERE))); } } } /** * list map, * ? . * * @param orders * @return */ public List<OrderListData> transformList(List<Order> orders) { List<OrderListData> resultList = new ArrayList(); for (Order order : orders) { String email = order.getClientEmail(); Long orderId = order.getOrderId(); List<Order> otherOrders; if (email == null || email.isEmpty()) { otherOrders = new ArrayList(); } else { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.add(Restrictions.eq("clientEmail", email)); crit.add(Restrictions.ne("orderId", orderId)); otherOrders = crit.list(); } OrderListData data = new OrderListData(); data.order = order; data.otherOrders = otherOrders; resultList.add(data); } return resultList; } public List<Order> getOtherOrders(String email, Order order) { Criteria crit = getCriteriaDistinctRootEntity(Order.class); crit.add(Restrictions.eq("clientEmail", email)); crit.add(Restrictions.ne("orderId", order.getOrderId())); return crit.list(); } public List<Map> getReportNew(Set<Long> orderTypeIds, Set<Long> directionIds, Set<Long> searchBrancheIds, Date from, Date to) { removeNill(searchBrancheIds, orderTypeIds, directionIds); String selectStr = " select new map(p.order as order, sum(p.amount) as amount_sum, p.paymentType as type) "; String fromStr = " from Payment as p join p.order as o "; String whereStr = " where p.paymentDate > :from and p.paymentDate < :to "; String groupStr = " group by p.order, p.paymentType "; boolean hasTypes = (orderTypeIds != null && !orderTypeIds.isEmpty()); boolean hasDirs = (directionIds != null && !directionIds.isEmpty()); boolean hasBranches = (searchBrancheIds != null && !searchBrancheIds.isEmpty()); if (hasTypes) { fromStr += " join o.orderType as ot "; whereStr += " and ot.orderTypeId in (:typeIds) "; } if (hasDirs) { fromStr += " join o.directions as dir "; whereStr += " and dir.directionId in (:dirIds) "; } if (hasBranches) { fromStr += " join o.branch as b "; whereStr += " and b.branchId in (:branchIds) "; } String queryStr = selectStr + fromStr + whereStr + groupStr; Query query = currentSession().createQuery(queryStr); query.setParameter("from", from); query.setParameter("to", to); if (hasTypes) { query.setParameterList("typeIds", orderTypeIds); } if (hasDirs) { query.setParameterList("dirIds", directionIds); } if (hasBranches) { query.setParameterList("branchIds", searchBrancheIds); } return query.list(); } public List<Order> getReport(Date from, Date to) { Query query = currentSession().createQuery( "select p from Payment as p join p.order as o where p.paymentDate > :from and p.paymentDate < :to"); query.setParameter("from", from); query.setParameter("to", to); List<Payment> payments = query.list(); Map<Long, Order> map = new HashMap(); for (Payment p : payments) { Order order = p.getOrder(); Long orderId = order.getId(); if (!map.containsKey(orderId)) { order.setPayments(new ArrayList()); map.put(orderId, order); } Order mapOrder = map.get(orderId); mapOrder.getPayments().add(p); } List<Order> list = new ArrayList(); list.addAll(map.values()); return list; } public List<Payment> getDetailReport(Date from, Date to, Long orderId) { Query query = currentSession().createQuery( "select p from Payment as p join p.order as o where p.paymentDate > :from and p.paymentDate < :to and o.orderId = :orderId"); query.setParameter("from", from); query.setParameter("to", to); query.setParameter("orderId", orderId); return query.list(); } /** * ?? * * @param orderTypeIds * @param directionIds * @param searchBrancheIds * @param from * @param to * @return */ public List<Payment> getReportAsPaymentList(Set<Long> orderTypeIds, Set<Long> directionIds, Set<Long> searchBrancheIds, Date from, Date to, Set<Long> rightBranchIds) { removeNill(orderTypeIds, directionIds, searchBrancheIds); from = support.DateUtils.startOfDay(from); to = support.DateUtils.endOfDay(to); Set<Long> branchIds = getBranchIds(searchBrancheIds, rightBranchIds); Criteria crit = currentSession().createCriteria(Payment.class); crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); crit.createAlias("order", "order"); crit.add(Restrictions.gt("paymentDate", from)); crit.add(Restrictions.lt("paymentDate", to)); if (orderTypeIds != null && !orderTypeIds.isEmpty()) { crit.createAlias("order.orderType", "orderType"); crit.add(Restrictions.in("orderType.orderTypeId", orderTypeIds)); } if (directionIds != null && !directionIds.isEmpty()) { crit.createAlias("order.directions", "directions"); crit.add(Restrictions.in("directions.directionId", directionIds)); } crit.createAlias("order.branch", "branch"); crit.add(Restrictions.in("branch.branchId", branchIds)); crit.addOrder(org.hibernate.criterion.Order.desc("paymentDate")); crit.addOrder(org.hibernate.criterion.Order.desc("amount")); return crit.list(); } private Set<Long> getBranchIds(Set<Long> searchBranchIds, Set<Long> rightBranchIds) { if (searchBranchIds == null || searchBranchIds.isEmpty()) { return rightBranchIds; } else { return CollectionUtils.crossing(searchBranchIds, rightBranchIds); } } private void removeNill(Set<Long>... sets) { for (Set<Long> set : sets) { if (set != null) { set.remove(Long.valueOf("0")); } } } }