Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit59e3efb

Browse files
authored
OBPIH-7529 select multiple origins on request details report (#5572)
1 parent3b45920 commit59e3efb

File tree

6 files changed

+247
-7
lines changed

6 files changed

+247
-7
lines changed
Lines changed: 114 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,114 @@
1+
packageorg.pih.warehouse.data
2+
3+
importgrails.gorm.transactions.Transactional
4+
importorg.apache.commons.lang3.NotImplementedException
5+
importorg.hibernate.Session
6+
importorg.hibernate.SessionFactory
7+
importorg.hibernate.query.NativeQuery
8+
importorg.hibernate.transform.AliasToEntityMapResultTransformer
9+
10+
importorg.pih.warehouse.core.PaginationParams
11+
12+
/**
13+
* Execute database queries via the Hibernate session.
14+
*
15+
* Usages of this service should auto-wire in PersistenceService (instead of HibernateSessionService) so that we
16+
* can swap out the implementation in the future if needed.
17+
*
18+
* Hibernate is an ORM (Object-Relational Mapping) tool. It is an implementation of the JPA (Java Persistence API)
19+
* specification. Hibernate provides an abstraction layer above the database, allowing us to query the database via
20+
* HQL (Hibernate Query Language) which is automatically translated to the language of the underlying database. This
21+
* allows us to support multiple SQL implementations (MariaDB, MySQL...) at once.
22+
*
23+
* If querying a domain object, use Spring Data's Repository pattern or GORM methods (provided by GormEntity) instead.
24+
* For example: Product.getByName(name) or Product.executeQuery(...).
25+
*/
26+
@Transactional
27+
classHibernateSessionServiceimplementsPersistenceService {
28+
29+
SessionFactory sessionFactory
30+
31+
privateSessiongetCurrentSession() {
32+
return sessionFactory.getCurrentSession()
33+
}
34+
35+
/**
36+
* Selects a list of rows from the database.
37+
*
38+
* Example usage:
39+
*
40+
* String sql = "SELECT * FROM location WHERE status IN (:statuses) and name = :name"
41+
* Map params = ["statuses", statuses, "name", name]
42+
* List<Map<String, Object>> result = HibernateSessionService.list(sql, params)
43+
*/
44+
List<Map<String,Object>>list(Stringsql,
45+
Map<String,Object>params=[:],
46+
PaginationParamspaginationParams=null) {
47+
NativeQuery query= createNativeQuery(sql, params)
48+
49+
if (paginationParams) {
50+
query.setMaxResults(paginationParams.max)
51+
.setFirstResult(paginationParams.offset)
52+
}
53+
54+
// Transforms the returned rows into a list of maps, keyed on column name.
55+
// Replace with TupleTransformer or ResultListTransformer when we upgrade to Hibernate 6+
56+
query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE)
57+
58+
return query.list()
59+
}
60+
61+
Map<String,Object>get(Stringsql,Map<String,Object>params) {
62+
thrownewNotImplementedException()
63+
}
64+
65+
intupdate(Stringsql,Map<String,Object>params) {
66+
thrownewNotImplementedException()
67+
}
68+
69+
intdelete(Stringsql,Map<String,Object>params) {
70+
thrownewNotImplementedException()
71+
}
72+
73+
/**
74+
* Initialize a NativeQuery object that can be used to query the database via native SQL. Automatically adds
75+
* the given params to the query object.
76+
*
77+
* For SELECT queries, use Query.list() or Query.uniqueResult() to extract the result.
78+
*
79+
* Example usage:
80+
*
81+
* String sql = "SELECT * FROM location WHERE status IN (:statuses) and name = :name"
82+
* Map params = ["statuses", statuses, "name", name]
83+
* List result = HibernateSessionService.createNativeQuery(sql, params).list()
84+
*/
85+
privateNativeQuerycreateNativeQuery(Stringsql,Map<String,Object>params) {
86+
NativeQuery query= currentSession.createNativeQuery(sql)
87+
return setParameters(query, params)
88+
}
89+
90+
privateNativeQuerysetParameters(NativeQueryquery,Map<String,Object>params) {
91+
if (!params) {
92+
return query
93+
}
94+
95+
for (entryin params) {
96+
String key= entry.key
97+
Object value= entry.value
98+
switch (value.class) {
99+
caseList:
100+
caseObject[]:
101+
query.setParameterList(key, value)
102+
break
103+
casenull:
104+
thrownewIllegalArgumentException("Param [${key}] has null value, which will cause SQL errors.")
105+
break
106+
default:
107+
query.setParameter(key, value)
108+
break
109+
}
110+
}
111+
112+
return query
113+
}
114+
}
Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
packageorg.pih.warehouse.data
2+
3+
importorg.pih.warehouse.core.PaginationParams
4+
5+
/**
6+
* For making queries to a persistence layer (ex: a database).
7+
*
8+
* If querying a domain object, use Spring Data's Repository pattern or GORM methods (provided by GormEntity) instead.
9+
* For example: Product.getByName(name) or Product.executeQuery(...).
10+
*/
11+
interfacePersistenceService {
12+
13+
/**
14+
* Selects a paginated list of rows from the persistence layer.
15+
*
16+
*@param query the query string
17+
*@param params a map of variables to be bound to the query. (For SQL, these are represented as ":x" in the query)
18+
*@param paginationParams required parameters for when we want to paginate the request
19+
*@return List<Map<String, Object>> the rows, each containing a map of columns, keyed on column name
20+
*/
21+
List<Map<String,Object>>list(Stringquery,
22+
Map<String,Object>params,
23+
PaginationParamspaginationParams)
24+
25+
/**
26+
* Selects a list of rows from the persistence layer.
27+
*
28+
*@param query the query string
29+
*@param params a map of variables to be bound to the query. (For SQL, these are represented as ":x" in the query)
30+
*@return List<Map<String, Object>> the rows, each containing a map of columns, keyed on column name
31+
*/
32+
List<Map<String,Object>>list(Stringquery,Map<String,Object>params)
33+
34+
/**
35+
* Selects a single row from the persistence layer.
36+
*
37+
*@param query the query string
38+
*@param params a map of variables to be bound to the query. (For SQL, these are represented as ":x" in the query)
39+
*@return Map<String, Object> a map of columns, keyed on column name
40+
*/
41+
Map<String,Object>get(Stringquery,Map<String,Object>params)
42+
43+
/**
44+
* Execute an update statement to the persistence layer.
45+
*
46+
*@param query the query string
47+
*@param params a map of variables to be bound to the query. (For SQL, these are represented as ":x" in the query)
48+
*@return int the number of entities updated
49+
*/
50+
intupdate(Stringquery,Map<String,Object>params)
51+
52+
/**
53+
* Execute a delete statement from the persistence layer.
54+
*
55+
*@param query the query string
56+
*@param params a map of variables to be bound to the query. (For SQL, these are represented as ":x" in the query)
57+
*@return int the number of entities deleted
58+
*/
59+
intdelete(Stringquery,Map<String,Object>params)
60+
}

‎grails-app/services/org/pih/warehouse/forecasting/ForecastingService.groovy‎

Lines changed: 25 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,11 @@ import grails.core.GrailsApplication
1313
importgrails.util.Holders
1414
importgroovy.sql.Sql
1515
importgroovy.time.TimeCategory
16+
importorg.springframework.beans.factory.annotation.Autowired
17+
importutil.RequestParamsUtil
18+
1619
importorg.pih.warehouse.core.Location
20+
importorg.pih.warehouse.data.PersistenceService
1721
importorg.pih.warehouse.product.Category
1822
importorg.pih.warehouse.product.Product
1923
importorg.pih.warehouse.DateUtil
@@ -27,6 +31,9 @@ import org.pih.warehouse.core.SynonymTypeCode
2731

2832
classForecastingService {
2933

34+
@Autowired
35+
PersistenceService persistenceService
36+
3037
def dataSource
3138
GrailsApplication grailsApplication
3239
def productAvailabilityService
@@ -248,6 +255,7 @@ class ForecastingService {
248255

249256
defgetRequestDetailReport(Mapparams) {
250257
List data= []
258+
Map<String,Object> queryParams= [:]
251259
String query="""
252260
select
253261
request_number,
@@ -275,30 +283,41 @@ class ForecastingService {
275283
query+=" LEFT JOIN product_catalog_item ON product_catalog_item.product_id = product_demand_details.product_id"
276284
}
277285

278-
query+=" WHERE date_issued BETWEEN :startDate AND :endDate AND origin_id = :originId"
286+
query+=" WHERE date_issued BETWEEN :startDate AND :endDate"
287+
queryParams.put("startDate", params.startDate)
288+
queryParams.put("endDate", params.endDate)
289+
290+
query+=" AND origin_id IN (:origins)"
291+
queryParams.put("origins",RequestParamsUtil.asList(params.originId))
279292

280293
if (params.destinationId) {
281294
query+=" AND destination_id = :destinationId"
295+
queryParams.put("destinationId", params.destinationId)
282296
}
283297
if (params.productId) {
284298
query+=" AND product_id = :productId"
299+
queryParams.put("productId", params.productId)
285300
}
286301
if (params.reasonCode) {
287302
query+=" AND reason_code_classification = :reasonCode"
303+
queryParams.put("reasonCode", params.reasonCode)
288304
}
289305
if (params.category) {
290306
Category category=Category.get(params.category)
291307
if (category) {
292-
def categories= category.children
308+
List<Category> categories= category.childrenasList<Category>
293309
categories<< category
294-
query+=" AND product.category_id in (${categories.collect { "'$it.id'" }.join(',')})"
310+
query+=" AND product.category_id in (:categories)"
311+
queryParams.put("categories", categories.id)
295312
}
296313
}
297314
if (params.tags&& params.tags!="null") {
298-
query+=" AND product_tag.tag_id in (${params.tags.split(",").collect { "'$it'" }.join(',')})"
315+
query+=" AND product_tag.tag_id in (:tags)"
316+
queryParams.put("tags", params.tags)
299317
}
300318
if (params.catalogs&& params.catalogs!="null") {
301-
query+=" AND product_catalog_item.product_catalog_id in (${params.catalogs.split(",").collect { "'$it'" }.join(',')})"
319+
query+=" AND product_catalog_item.product_catalog_id in (:catalogs)"
320+
queryParams.put("catalogs", params.catalogs)
302321
}
303322

304323
if ((params.tags&& params.tags!="null")|| (params.catalogs&& params.catalogs!="null")) {
@@ -307,9 +326,8 @@ class ForecastingService {
307326
" quantity_requested, quantity_picked, reason_code_classification, quantity_demand"
308327
}
309328

310-
Sql sql=newSql(dataSource)
311329
try {
312-
data=sql.rows(query,params)
330+
data=persistenceService.list(query,queryParams)
313331

314332
}catch (Exception e) {
315333
log.error("Unable to execute query:"+ e.message, e)

‎grails-app/views/report/showRequestDetailReport.gsp‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@
2222
name="origin.id"
2323
noSelection="['':'']"
2424
groupBy="locationType"
25+
multiple="multiple"
2526
value="${params?.origin}"/>
2627
</div>
2728
<divclass="filter-list-item">
Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,28 @@
1+
packageutil
2+
3+
/**
4+
* Utility methods for processing API request and query parameters.
5+
*/
6+
classRequestParamsUtil {
7+
8+
/**
9+
* Binds a multi-value query param of the format ?x=1,2,3 or ?x=1&x=2&x=3 (the latter is preferred)
10+
* to a list of strings.
11+
*/
12+
staticList<String>asList(Objectparam) {
13+
switch (param.class) {
14+
casenull:
15+
returnnull
16+
// Grails will automatically bind query params to an array if they're in the format ?x=1&x=2&x=3
17+
// so in that case all we need to do is convert to a list
18+
caseList:
19+
caseObject[]:
20+
return paramasList<String>
21+
// If we're given query params in the format ?x=1,2,3 then we need to process them ourselves
22+
caseString:
23+
returnStringUtil.split(param)
24+
default:
25+
thrownewIllegalArgumentException("Invalid type in request param:${param.class}")
26+
}
27+
}
28+
}

‎src/main/groovy/util/StringUtil.groovy‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -12,12 +12,15 @@ package util
1212
importgroovy.text.SimpleTemplateEngine
1313

1414
importjava.text.MessageFormat
15+
importorg.apache.commons.lang.StringUtils
1516

1617
/**
1718
* Utility methods for parsing/formatting strings and general string manipulation.
1819
*/
1920
classStringUtil {
2021

22+
privatestaticfinalStringDEFAULT_DELIMITER=","
23+
2124
staticStringmask(Stringvalue) {
2225
return mask(value,"*")
2326
}
@@ -41,4 +44,20 @@ class StringUtil {
4144
.toLowerCase()
4245
.capitalize()
4346
}
47+
48+
/**
49+
* Splits a given string into a list of strings separated by the given separator.
50+
* Ex: Given "x,y,z", returns ["x","y","z"]
51+
*/
52+
staticList<String>split(Objectvalue,Stringdelimiter=DEFAULT_DELIMITER) {
53+
if (!value) {
54+
returnnull
55+
}
56+
if (!(valueinstanceofString)) {
57+
thrownewIllegalArgumentException("Expected String but got${value.class}")
58+
}
59+
60+
String valueString= valueasString
61+
returnStringUtils.isBlank(valueString)? []: valueString.split(delimiter).toList()
62+
}
4463
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp