- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 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 строк. пожалуйста выскажите ваше мнение об этом коде. спасибо :)