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

Commitf8deac9

Browse files
authored
OBPIH-7542 Create backend for new reorder report (#5568)
1 parent6783694 commitf8deac9

File tree

9 files changed

+303
-0
lines changed

9 files changed

+303
-0
lines changed

‎grails-app/controllers/org/pih/warehouse/UrlMappings.groovy‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -820,6 +820,11 @@ class UrlMappings {
820820
action="importCsv"
821821
}
822822

823+
"/api/facilities/$facilityId/inventories/reorderReport" {
824+
controller= {"inventoryApi" }
825+
action= [GET:"getReorderReport"]
826+
}
827+
823828
/**
824829
* Purchase Orders API endpoints
825830
*/

‎grails-app/controllers/org/pih/warehouse/api/InventoryApiController.groovy‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,20 @@
11
packageorg.pih.warehouse.api
22

3+
importgrails.converters.JSON
4+
importgrails.validation.ValidationException
5+
importorg.pih.warehouse.auth.AuthService
6+
importorg.pih.warehouse.core.DashboardService
37
importorg.pih.warehouse.core.Location
48
importorg.pih.warehouse.importer.CSVUtils
59
importorg.pih.warehouse.importer.ImportDataCommand
610
importorg.pih.warehouse.importer.InventoryImportDataService
11+
importorg.pih.warehouse.inventory.ReorderReportFilterCommand
12+
importorg.pih.warehouse.inventory.ReorderReportItemDto
713

814
classInventoryApiController {
915

1016
InventoryImportDataService inventoryImportDataService
17+
DashboardService dashboardService
1118

1219
defimportCsv() {
1320
String fileData= request.inputStream.text
@@ -31,4 +38,25 @@ class InventoryApiController {
3138

3239
render(status:200)
3340
}
41+
42+
defgetReorderReport(ReorderReportFilterCommandcommand) {
43+
if (command.hasErrors()) {
44+
thrownewValidationException("Invalid filters", command.errors)
45+
}
46+
List<ReorderReportItemDto> reorderReport= dashboardService.getReorderReport(command)
47+
48+
withFormat {
49+
"csv" {
50+
String csv= dashboardService.getReorderReportCsv(reorderReport)
51+
response.contentType="text/csv"
52+
String filename="Reorder report -${AuthService.currentLocation?.name}.csv"
53+
response.setHeader("Content-disposition","attachment; filename=\"${filename}\"")
54+
render(csv)
55+
return
56+
}
57+
"*" {
58+
render([data: reorderReport]asJSON)
59+
}
60+
}
61+
}
3462
}

‎grails-app/i18n/messages.properties‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1412,6 +1412,7 @@ inventory.cycleCount.label=Cycle count
14121412
inventory.manageCycleCount.label=Manage Cycle Count
14131413
inventory.performCycleCount.label=Perform Cycle Count
14141414
inventory.reporting.label=Reporting
1415+
inventory.expectedReorderCost.label=Expected Reorder Cost
14151416

14161417
stockTransfer.label=Stock Transfer
14171418
outboundReturns.temporaryDisabled.message=Creating Outbound Returns was temporary disabled
@@ -2287,6 +2288,10 @@ putawayOrder.preferredBin.label=Preferred Bins
22872288
putawayOrder.currentBins.label=Current Bins
22882289
putawayOrder.putAwayBin.label=Putaway Bin
22892290

2291+
# Reorder report messages
2292+
reorderReportFilterCommand.additionalLocations.invalid.locations=Every additional location has to support Manage Inventory activity
2293+
reorderReport.noMaxQtySet.label=No Max qty set - review based on monthly demand
2294+
22902295
# Receipt messages
22912296
receipts.label=Receipts
22922297
receipt.actualDeliveryDate.invalid.mustOccurOnOrAfterActualShippingDate=Must occur on or after Actual Shipping Date

‎grails-app/services/org/pih/warehouse/core/DashboardService.groovy‎

Lines changed: 136 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,13 +11,23 @@ package org.pih.warehouse.core
1111

1212
importgrails.gorm.transactions.Transactional
1313
importgrails.plugins.csv.CSVWriter
14+
importgrails.util.Holders
15+
importorg.grails.plugins.web.taglib.ApplicationTagLib
1416
importorg.hibernate.sql.JoinType
1517

1618
importorg.pih.warehouse.DateUtil
19+
importorg.pih.warehouse.auth.AuthService
20+
importorg.pih.warehouse.core.localization.MessageLocalizer
21+
importorg.pih.warehouse.forecasting.ForecastingService
22+
importorg.pih.warehouse.importer.CSVUtils
1723
importorg.pih.warehouse.inventory.InventoryItem
1824
importorg.pih.warehouse.inventory.InventoryLevel
25+
importorg.pih.warehouse.inventory.InventoryLevelStatus
1926
importorg.pih.warehouse.inventory.InventoryStatus
27+
importorg.pih.warehouse.inventory.ReorderReportFilterCommand
28+
importorg.pih.warehouse.inventory.ReorderReportItemDto
2029
importorg.pih.warehouse.inventory.TransactionEntry
30+
importorg.pih.warehouse.inventory.product.availability.InventoryByProduct
2131
importorg.pih.warehouse.product.Category
2232
importorg.pih.warehouse.product.Product
2333
importorg.pih.warehouse.report.InventoryReportCommand
@@ -31,6 +41,9 @@ class DashboardService {
3141

3242
ConfigService configService
3343
def productAvailabilityService
44+
ForecastingService forecastingService
45+
UserService userService
46+
MessageLocalizer messageLocalizer
3447

3548
/**
3649
* Get fast moving items based on requisition data.
@@ -573,6 +586,129 @@ class DashboardService {
573586
return reorderStock
574587
}
575588

589+
List<ReorderReportItemDto>getReorderReport(ReorderReportFilterCommandcommand) {
590+
// Find inventory levels that don't have bin location set and have AT LEAST one of min/max qty set
591+
List<InventoryLevel> inventoryLevels=InventoryLevel.createCriteria().list {
592+
// Current location is a "central" one, we want to get inventory level values (max/min qty) only from the current location
593+
// additionaLocations are supposed to be used only for the quantity available to promise calculation,
594+
// where we sum the QATP for currentLocation + additionalLocations
595+
eq("inventory",AuthService.currentLocation.inventory)
596+
isNull("internalLocation")
597+
or {
598+
and {
599+
isNotNull("minQuantity")
600+
gt("minQuantity",0)
601+
}
602+
and {
603+
isNotNull("reorderQuantity")
604+
gt("reorderQuantity",0)
605+
}
606+
}
607+
}
608+
List<InventoryByProduct> inventoriesByProduct= productAvailabilityService.getInventoriesByProduct(command.additionalLocations,
609+
command.categories,
610+
command.tags,
611+
command.expiration)
612+
613+
// Build a map of inventory levels keyed on product to have O(1) read access in the .findResults below instead of O(n) if we were to do .find on List
614+
Map<Product,InventoryLevel> inventoryLevelByProduct= inventoryLevels.collectEntries { [it.product, it] }
615+
616+
// .findResults is a shortened way of doing .findAll{...}.collect{...}
617+
List<ReorderReportItemDto> reorderReportItems= inventoriesByProduct.findResults {InventoryByProductitem->
618+
InventoryLevel inventoryLevel= inventoryLevelByProduct[item.product]
619+
620+
// Depending on the provided inventory level status filter value, we have a different condition for filtering out the items
621+
Map<InventoryLevelStatus,Closure<Boolean>> inventoryLevelStatusFilterCondition= [
622+
(InventoryLevelStatus.IN_STOCK): { item.quantityAvailableToPromise>0 },
623+
(InventoryLevelStatus.BELOW_MAXIMUM): { inventoryLevel?.maxQuantity&& item.quantityAvailableToPromise<= inventoryLevel?.maxQuantity },
624+
(InventoryLevelStatus.BELOW_REORDER): { inventoryLevel?.reorderQuantity&& item.quantityAvailableToPromise<= inventoryLevel?.reorderQuantity },
625+
(InventoryLevelStatus.BELOW_MINIMUM): { inventoryLevel?.minQuantity&& item.quantityAvailableToPromise<= inventoryLevel?.minQuantity }
626+
]
627+
628+
// If an item satisfies the predicate, call the buildReorderReportItem, otherwise return null so that .findResults filters out the record in the end
629+
return inventoryLevelStatusFilterCondition[command.inventoryLevelStatus](item)? buildReorderReportItem(item, inventoryLevel):null
630+
}
631+
632+
return reorderReportItems
633+
}
634+
635+
privateReorderReportItemDtobuildReorderReportItem(InventoryByProductitem,InventoryLevelinventoryLevel) {
636+
Map<String,Number> monthlyDemand= forecastingService.getDemand(AuthService.currentLocation,null, item.product)
637+
Integer quantityToOrder= inventoryLevel?.maxQuantity!=null? inventoryLevel.maxQuantity- item.quantityAvailableToPromise:null
638+
Boolean hasRoleFinance= userService.hasRoleFinance(AuthService.currentUser)
639+
BigDecimal unitCost= hasRoleFinance? item.product.pricePerUnit:null
640+
BigDecimal expectedReorderCost= hasRoleFinance&& quantityToOrder&& item.product.pricePerUnit!=null
641+
? quantityToOrder* unitCost
642+
:null
643+
Closure determineInventoryStatus= {InventoryLevelinventoryLevel1,IntegerquantityOnHand->
644+
if (inventoryLevel1) {
645+
return inventoryLevel1.statusMessage(quantityOnHand)
646+
}
647+
return quantityOnHand>0?"IN_STOCK":"STOCK_OUT"
648+
}
649+
returnnewReorderReportItemDto(
650+
inventoryStatus: determineInventoryStatus(inventoryLevel, item.quantityOnHand),
651+
product: item.product,
652+
tags: item.product.tags,
653+
inventoryLevel: inventoryLevel,
654+
monthlyDemand: monthlyDemand?.monthlyDemand?:0,
655+
quantityAvailableToPromise: item.quantityAvailableToPromise,
656+
quantityToOrder: quantityToOrder,
657+
unitCost: unitCost,
658+
expectedReorderCost: expectedReorderCost
659+
)
660+
}
661+
662+
StringgetReorderReportCsv(List<ReorderReportItemDto>reorderReport) {
663+
StringWriter sw=newStringWriter()
664+
Boolean hasRoleFinance= userService.hasRoleFinance(AuthService.currentUser)
665+
CSVWriter csv=newCSVWriter(sw, {
666+
"${messageLocalizer.localize("inventoryLevel.status.label")}" { it?.status }
667+
"${messageLocalizer.localize("product.productCode.label")}" { it?.productCode }
668+
"${messageLocalizer.localize("product.label")}" { it?.product }
669+
"${messageLocalizer.localize("category.label")}" { it?.category }
670+
"${messageLocalizer.localize("product.tags.label", "Tags")}" { it?.tags }
671+
"${messageLocalizer.localize("product.unitOfMeasure.label", "Unit of measure")}" { it?.unitOfMeasure }
672+
"${messageLocalizer.localize("product.vendor.label", "Vendor")}" { it?.vendor }
673+
"${messageLocalizer.localize("product.vendorCode.label", "Vendor code")}" { it?.vendorCode }
674+
"${messageLocalizer.localize("inventoryLevel.minQuantity.label", "Min quantity")}" { it?.minQuantity }
675+
"${messageLocalizer.localize("inventoryLevel.reorderQuantity.label", "Reorder quantity")}" { it?.reorderQuantity }
676+
"${messageLocalizer.localize("inventoryLevel.maxQuantity.label", "Max quantity")}" { it?.maxQuantity }
677+
"${messageLocalizer.localize("report.averageMonthlyDemand.label", "Average Monthly Demand")}" { it?.averageMonthlyDemand }
678+
"${messageLocalizer.localize("product.quantityAvailableToPromise.label", "Quantity Available")}" { it?.quantityAvailableToPromise }
679+
"${messageLocalizer.localize("inventory.quantityToOrder.message", "Quantity to Order")}" { it?.quantityToOrder }
680+
681+
if (hasRoleFinance) {
682+
"${messageLocalizer.localize("product.unitCost.label", "Unit Cost")}" { it?.unitCost }
683+
"${messageLocalizer.localize("inventory.expectedReorderCost.label", "Expected Reorder Cost")}" { it?.expectedReorderCost }
684+
}
685+
})
686+
Closure getQuantityToOrderDisplayValue= {IntegermaxQuantity,IntegerquantityToOrder->
687+
return maxQuantity==null? messageLocalizer.localize("reorderReport.noMaxQtySet.label"): quantityToOrder
688+
}
689+
reorderReport.each {ReorderReportItemDtoitem->
690+
csv<< [
691+
status:"${messageLocalizer.localize("enum.InventoryLevelStatusCsv." + item.inventoryStatus)}",
692+
productCode: item.product.productCode,
693+
product: item.product.displayNameOrDefaultName,
694+
category: item.product.category?.name?:"",
695+
tags: item.product.tagsToString(),
696+
unitOfMeasure: item.product.unitOfMeasure?:"",
697+
vendor: item.product.vendor?:"",
698+
vendorCode: item.product.vendorCode?:"",
699+
minQuantity: item.inventoryLevel?.minQuantity?:"",
700+
reorderQuantity: item.inventoryLevel?.reorderQuantity?:"",
701+
maxQuantity: item.inventoryLevel?.maxQuantity?:"",
702+
averageMonthlyDemand: item.monthlyDemand?:0,
703+
quantityAvailableToPromise: item.quantityAvailableToPromise,
704+
quantityToOrder: getQuantityToOrderDisplayValue(item.inventoryLevel?.maxQuantity, item.quantityToOrder)?:"",
705+
unitCost: item.unitCost?:"",
706+
expectedReorderCost: item.expectedReorderCost?:"",
707+
]
708+
}
709+
returnCSVUtils.prependBomToCsvString(csv.writer.toString())
710+
}
711+
576712
defgetReorderReport(Locationlocation) {
577713
long startTime=System.currentTimeMillis()
578714
ArrayList<Map> inventoryItems= getInventoryItems(location)

‎grails-app/services/org/pih/warehouse/inventory/ProductAvailabilityService.groovy‎

Lines changed: 61 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -18,20 +18,25 @@ import groovy.sql.Sql
1818
importorg.apache.commons.lang.StringEscapeUtils
1919
importorg.hibernate.Criteria
2020
importorg.hibernate.criterion.CriteriaSpecification
21+
importorg.hibernate.criterion.Criterion
2122
importorg.hibernate.criterion.DetachedCriteria
2223
importorg.hibernate.criterion.Projections
2324
importorg.hibernate.criterion.Restrictions
2425
importorg.hibernate.criterion.Subqueries
2526
importorg.hibernate.SQLQuery
27+
importorg.hibernate.sql.JoinType
2628
importorg.hibernate.type.StandardBasicTypes
2729
importorg.pih.warehouse.PaginatedList
2830
importorg.pih.warehouse.api.AllocatedItem
2931
importorg.pih.warehouse.api.AvailableItem
32+
importorg.pih.warehouse.auth.AuthService
3033
importorg.pih.warehouse.core.ApplicationExceptionEvent
3134
importorg.pih.warehouse.core.Constants
3235
importorg.pih.warehouse.core.Location
36+
importorg.pih.warehouse.core.Tag
3337
importorg.pih.warehouse.core.db.GormUtil
3438
importorg.pih.warehouse.inventory.product.availability.AvailableItemMap
39+
importorg.pih.warehouse.inventory.product.availability.InventoryByProduct
3540
importorg.pih.warehouse.jobs.RefreshProductAvailabilityJob
3641
importorg.pih.warehouse.order.OrderStatus
3742
importorg.pih.warehouse.product.Category
@@ -520,6 +525,62 @@ class ProductAvailabilityService {
520525
return quantityMap
521526
}
522527

528+
/**
529+
* Find product availability record grouped by product that satisfy the filters which contain primarily:
530+
* expiration (we can include/exclude inventory items that expire in 30/90/180/365 days),
531+
* additionalLocations - locations to search (and sum) the stock in (other than current location)
532+
*/
533+
List<InventoryByProduct>getInventoriesByProduct(List<Location>additionalLocations,
534+
List<Category>categories,
535+
List<Tag>productTags,
536+
ExpirationFilterexpiration) {
537+
List<Location> locations= [AuthService.currentLocation]
538+
if (additionalLocations) {
539+
locations.addAll(additionalLocations)
540+
}
541+
returnProductAvailability.createCriteria().list {
542+
projections {
543+
sum("quantityOnHand")
544+
sum("quantityAvailableToPromise")
545+
groupProperty("product")
546+
}
547+
inList("location", locations)
548+
549+
// The additional if check is needed to avoid joining product twice in two separate ifs for categories/tags, as it could throw an error
550+
// if both categories and tags were provided. Another possible solution would be to store "usedAliases" and check if categories already joined the product.
551+
if (categories|| productTags) {
552+
product {
553+
if (categories) {
554+
inList("category", categories)
555+
}
556+
if (productTags) {
557+
tags {
558+
'in'("id", productTags.id)
559+
}
560+
}
561+
}
562+
}
563+
564+
if (expiration!=ExpirationFilter.INCLUDE_EXPIRED_STOCK) {
565+
add(getExpirationCriteria(expiration, delegate))
566+
}
567+
}.collect {newInventoryByProduct(
568+
quantityOnHand: it[0],
569+
quantityAvailableToPromise: it[1],
570+
product: it[2]
571+
)}
572+
}
573+
574+
privatestaticCriteriongetExpirationCriteria(ExpirationFilterexpirationFilter,org.grails.datastore.mapping.query.api.Criteriacriteria) {
575+
criteria.createAlias("inventoryItem","ii",JoinType.INNER_JOIN)
576+
// We have to include the condition for qoh > 0, as we could potentially include items that are out of stock, but have "hidden" associated
577+
// inventory item with product availability record that is not visible in the stock history at that time
578+
if (expirationFilter==ExpirationFilter.REMOVE_EXPIRED_STOCK) {
579+
returnRestrictions.and(Restrictions.ge("ii.expirationDate",newDate()),Restrictions.gt("quantityOnHand",0))
580+
}
581+
returnRestrictions.and(Restrictions.between("ii.expirationDate",newDate(),newDate()+ expirationFilter.days),Restrictions.gt("quantityOnHand",0))
582+
}
583+
523584
Map<Product,Integer>getQuantityOnHandByProduct(Locationlocation) {
524585
def quantityMap= [:]
525586
if (location) {
Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
packageorg.pih.warehouse.inventory
2+
3+
enumExpirationFilter {
4+
INCLUDE_EXPIRED_STOCK(null),
5+
REMOVE_EXPIRED_STOCK(null),
6+
EXPIRING_WITHIN_MONTH(30),
7+
EXPIRING_WITHIN_QUARTER(90),
8+
EXPIRING_WITHIN_HALF_YEAR(180),
9+
EXPIRING_WITHIN_YEAR(365)
10+
11+
Integer days
12+
13+
privateExpirationFilter(Integerdays) {
14+
this.days= days
15+
}
16+
}
Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,27 @@
1+
packageorg.pih.warehouse.inventory
2+
3+
importgrails.validation.Validateable
4+
importorg.pih.warehouse.core.ActivityCode
5+
importorg.pih.warehouse.core.Location
6+
importorg.pih.warehouse.core.Tag
7+
importorg.pih.warehouse.product.Category
8+
9+
classReorderReportFilterCommandimplementsValidateable {
10+
List<Location> additionalLocations
11+
InventoryLevelStatus inventoryLevelStatus=InventoryLevelStatus.IN_STOCK
12+
ExpirationFilter expiration=ExpirationFilter.REMOVE_EXPIRED_STOCK
13+
List<Category> categories
14+
List<Tag> tags
15+
16+
static constraints= {
17+
additionalLocations(nullable:true,validator: {List<Location>locations->
18+
if (!locations.every { it.supports(ActivityCode.MANAGE_INVENTORY) }) {
19+
return ['invalid.locations']
20+
}
21+
})
22+
inventoryLevelStatus(nullable:true)
23+
expiration(nullable:true)
24+
categories(nullable:true)
25+
tags(nullable:true)
26+
}
27+
}
Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
packageorg.pih.warehouse.inventory
2+
3+
importorg.pih.warehouse.core.Tag
4+
importorg.pih.warehouse.product.Product
5+
6+
classReorderReportItemDto {
7+
String inventoryStatus
8+
Product product
9+
Set<Tag> tags
10+
InventoryLevel inventoryLevel
11+
Integer monthlyDemand
12+
Integer quantityAvailableToPromise
13+
Integer quantityToOrder
14+
BigDecimal unitCost
15+
BigDecimal expectedReorderCost
16+
}

0 commit comments

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