- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
Query query = entityManager.createNativeQuery(
"select * from ("
+ "select g4.id as goodId, g4.name as goodName, g4.code as goodCode, "
+ "u4.name as uomName, sum(bgd.quantity_out) as quantityOut, "
+ " g4.productcode as goodProductCode, sum(bgd.quantity_return) as quantityReturn "
+ "from good g4 "
+ "inner join consignment cs1 on cs1.good_id = g4.id "
+ (customizationUtilSQL == null ? "" : customizationUtilSQL.buildCustomAttributesQueryInnerPart())
+ " inner join "
+ "(select gg.consignment_id, op.sourcestore_id as place_id, " +
" sum(gg.quantity) as quantity_out, sum(coalesce(srset.return_quantity, 0)) as quantity_return "
+ "from motion gg "
+ "inner join operation op " +
" on (gg.operation_id = op.id " +
" and op.company = gg.company" +
(projectId == null ? "" : " and (op.project_id = :projectId) ") +
(contractId == null ? "" : " and (op.contract_id = :contractId) ") +
") "
+ "inner join place pld on op.sourcestore_id = pld.id "
+ " left outer join "
+ " (select ds.demand_id as demand_id, srm.consignment_id as consignment_id, srm.sum as sum, " +
" sum(srm.quantity) as return_quantity from demand_salesreturn ds " +
" inner join operation sr on sr.id = ds.salesreturn_id " +
" inner join motion srm on srm.operation_id = sr.id " +
" where sr.dtype = 'SalesReturn' " +
" and sr.deleted is null " +
(!includeAboardOperations ? " and sr.applicable = true " : "") +
" and (sr.moment between :upToDate and :toDate) " +
" and " + SecurityUtil.companyFilterSQL("sr") +
" group by ds.demand_id, srm.consignment_id, srm.sum) srset " +
" on (srset.demand_id = op.id and srset.sum = gg.sum " +
" and srset.consignment_id = gg.consignment_id) "
+ (agentId == null ? "" :
" left outer join requisite tr on op.targetagentrequisite_id = tr.id "
+ " left outer join requisite sr on op.sourceagentrequisite_id = sr.id "
+ " left outer join agent agt on tr.agent_id = agt.id "
+ " left outer join agent ags on sr.agent_id = ags.id ")
+ "where " + SecurityUtil.companyFilterSQL("op")
+ " and op.deleted is null "
+ (!includeAboardOperations ? " and op.applicable = true " : "")
+ " and (op.moment between :upToDate and :toDate) "
+ " and op.dtype = 'Demand' "
+ (agentId == null ? ""
: " and ((ags.path || ags.id) like ( "
+ "select distinct (a21.path || a21.id || '%') from agent a21 where a21.id = :agentId) or "
всё не влезло, вообще тут 65 строк. пожалуйста выскажите ваше мнение об этом коде. спасибо :)
+ "select distinct (a22.path || a22.id || '%') from agent a22 where a22.id = :agentId)) ")
+ (storeId == null ? ""
: " and (pld.path || pld.id) like ( "
+ "select distinct (p2.path || p2.id || '%') from "
+ "place p2 where p2.id = :storeId) ")
+ " group by gg.consignment_id, op.sourcestore_id) as bgd on cs1.id = bgd.consignment_id "
+ " left outer join uom u4 on g4.uom_id = u4.id "
+ " where 1 = 1 "
+ (goodId == null ? "" :
" and (g4.path || g4.id) like (select distinct (g31.path || g31.id || '%') from good g31 where g31.id = :goodId) ")
+ (supplierId == null ? ""
: " and (g4.supplier_id = :supplierId) ")
+ (customizationUtilSQL == null ? " "
: customizationUtilSQL.buildCustomAttribut esQueryWherePart(false, true))
+ " group by g4.id, g4.name, g4.code, u4.name, g4.productcode "
+ " having (sum(bgd.quantity_out) - sum(bgd.quantity_return) > :underZero)"
+ ") fullquery "
+ sorting.toString())
дохуища SQL-УГ, которого мы навидались до рвоты в разделе PHP
не смешно и нет желания нырять в это с головой.
UPD: капча не сработала, аднака =\
капча была запилена для того, чтобы больные мишустиазом не могли постить много подзалупного творожка в автоматическом режиме. соответственно, работает по принципу rate-limitera и не пропускает каменты, если юзер постит более чем N комментов/секунду. показ капчи подчиняется тому же принципу.
и хватит об этом, ибо боян
темболее, сколько времени на анализаторе показывает выполнение этого запроса?
Chrząszczyżewoszyce powiat Łękołody