def basequeryset(reportclass, request, *args, **kwargs):
q = Demand.objects.all()
if "item" in request.GET:
Item.rebuildHierarchy(request.database)
item = Item.objects.using(
request.database).get(name__exact=unquote(request.GET["item"]))
q = q.filter(item__lft__gte=item.lft, item__lft__lt=item.rght)
if "location" in request.GET:
Location.rebuildHierarchy(request.database)
location = Location.objects.using(request.database).get(
name__exact=unquote(request.GET["location"]))
q = q.filter(location__lft__gte=location.lft,
location__lft__lt=location.rght)
if "customer" in request.GET:
Customer.rebuildHierarchy(request.database)
customer = Customer.objects.using(request.database).get(
name__exact=unquote(request.GET["customer"]))
q = q.filter(customer_lft__gte=customer.lft,
customer_lft__lt=customer.rght)
if "status_in" in request.GET:
status = unquote(request.GET["status_in"])
q = q.filter(status__in=status.split(","))
return q.annotate(
plannedshort=RawSQL("quantity - plannedquantity", []))
def query(reportclass, request, basequery):
'''
A function that recurses upstream or downstream in the supply chain.
'''
entity = basequery.query.get_compiler(basequery.db).as_sql(with_col_aliases=False)[1]
entity = entity[0]
root = reportclass.getRoot(request, entity)
# Update item and location hierarchies
Item.rebuildHierarchy(database=request.database)
Location.rebuildHierarchy(database=request.database)
# Recurse over all operations
# TODO the current logic isn't generic enough. A lot of buffers may not be explicitly
# defined, and are created on the fly by deliveries, itemsuppliers or itemdistributions.
# Currently we don't account for such situations.
# TODO usage search doesn't find item distributions from that location
counter = 1
#operations = set()
while len(root) > 0:
# Pop the current node from the stack
level, parent, curoperation, curqty, issuboperation, parentoper, realdepth, pushsuper, location = root.pop()
curnode = counter
counter += 1
# If an operation has parent operations we forget about the current operation
# and use only the parent
if pushsuper and not isinstance(curoperation, (ItemSupplier, ItemDistribution)):
hasParents = False
for x in curoperation.superoperations.using(request.database).only('operation').order_by("-priority"):
root.append( (level, parent, x.operation, curqty, issuboperation, parentoper, realdepth, False, location) )
hasParents = True
if hasParents:
continue
# Avoid showing the same operation twice.
# This feature is disabled by default a) because it is not intuitive to understand
# where operations are skipped, and b) because the quantity of each occurrence might
# be different.
# In some models the duplication is confusing and you can enable this feature.
#if curoperation in operations: continue
#operations.add(curoperation)
# Find the next level
hasChildren = False
subcount = 0
if reportclass.downstream:
# Downstream recursion
if isinstance(curoperation, ItemSupplier):
name = 'Purchase %s @ %s from %s' % (curoperation.item.name, location, curoperation.supplier.name)
optype = "purchase"
duration = curoperation.leadtime
duration_per = None
buffers = [ ("%s @ %s" % (curoperation.item.name, curoperation.location.name), 1), ]
resources = None
try:
downstr = Buffer.objects.using(request.database).get(name="%s @ %s" % (curoperation.item.name, curoperation.location.name))
root.extend( reportclass.findUsage(downstr, request.database, level, curqty, realdepth + 1, False, location) )
except Buffer.DoesNotExist:
pass
elif isinstance(curoperation, ItemDistribution):
name = 'Ship %s from %s to %s' % (curoperation.item.name, curoperation.origin.name, curoperation.location.name)
optype = "distribution"
duration = curoperation.leadtime
duration_per = None
buffers = [
("%s @ %s" % (curoperation.item.name, curoperation.origin.name), -1),
("%s @ %s" % (curoperation.item.name, curoperation.location.name), 1)
]
resources = None
else:
name = curoperation.name
optype = curoperation.type
duration = curoperation.duration
duration_per = curoperation.duration_per
buffers = [ (x.thebuffer.name, float(x.quantity)) for x in curoperation.flows.only('thebuffer', 'quantity').using(request.database) ]
resources = [ (x.resource.name, float(x.quantity)) for x in curoperation.loads.only('resource', 'quantity').using(request.database) ]
for x in curoperation.flows.filter(quantity__gt=0).only('thebuffer').using(request.database):
curflows = x.thebuffer.flows.filter(quantity__lt=0).only('operation', 'quantity').using(request.database)
for y in curflows:
hasChildren = True
root.append( (level - 1, curnode, y.operation, - curqty * y.quantity, subcount, None, realdepth - 1, pushsuper, x.thebuffer.location.name if x.thebuffer.location else None) )
for x in curoperation.suboperations.using(request.database).only('suboperation').order_by("-priority"):
subcount += curoperation.type == "routing" and 1 or -1
root.append( (level - 1, curnode, x.suboperation, curqty, subcount, curoperation, realdepth, False, location) )
hasChildren = True
else:
# Upstream recursion
if isinstance(curoperation, ItemSupplier):
name = 'Purchase %s @ %s from %s' % (curoperation.item.name, location, curoperation.supplier.name)
optype = "purchase"
duration = curoperation.leadtime
duration_per = None
buffers = [ ("%s @ %s" % (curoperation.item.name, location), 1), ]
resources = None
elif isinstance(curoperation, ItemDistribution):
name = 'Ship %s from %s to %s' % (curoperation.item.name, curoperation.origin.name, location)
optype = "distribution"
duration = curoperation.leadtime
duration_per = None
buffers = [
("%s @ %s" % (curoperation.item.name, curoperation.origin.name), -1),
("%s @ %s" % (curoperation.item.name, curoperation.location.name), 1)
]
resources = None
try:
upstr = Buffer.objects.using(request.database).get(name="%s @ %s" % (curoperation.item.name, curoperation.origin.name))
root.extend( reportclass.findReplenishment(upstr, request.database, level + 2, curqty, realdepth + 1, False) )
except Buffer.DoesNotExist:
pass
else:
curprodflow = None
name = curoperation.name
optype = curoperation.type
duration = curoperation.duration
duration_per = curoperation.duration_per
buffers = [ (x.thebuffer.name, float(x.quantity)) for x in curoperation.flows.only('thebuffer', 'quantity').using(request.database) ]
resources = [ (x.resource.name, float(x.quantity)) for x in curoperation.loads.only('resource', 'quantity').using(request.database) ]
for x in curoperation.flows.filter(quantity__gt=0).only('quantity').using(request.database):
curprodflow = x
curflows = curoperation.flows.filter(quantity__lt=0).only('thebuffer', 'quantity').using(request.database)
for y in curflows:
if y.thebuffer.producing:
hasChildren = True
root.append( (
level + 1, curnode, y.thebuffer.producing,
curprodflow and (-curqty * y.quantity) / curprodflow.quantity or (-curqty * y.quantity),
subcount, None, realdepth + 1, True, y.thebuffer.location
) )
else:
root.extend( reportclass.findReplenishment(y.thebuffer, request.database, level + 2, curqty, realdepth + 1, False) )
for x in curoperation.suboperations.using(request.database).only('suboperation').order_by("-priority"):
subcount += curoperation.type == "routing" and 1 or -1
root.append( (level + 1, curnode, x.suboperation, curqty, subcount, curoperation, realdepth, False, location) )
hasChildren = True
# Process the current node
yield {
'depth': abs(level),
'id': curnode,
'operation': name,
'type': optype,
'location': curoperation.location and curoperation.location.name or '',
'duration': duration,
'duration_per': duration_per,
'quantity': curqty,
'suboperation': issuboperation,
'buffers': buffers,
'resources': resources,
'parentoper': parentoper and parentoper.name,
'parent': parent,
'leaf': hasChildren and 'false' or 'true',
'expanded': 'true',
'numsuboperations': subcount,
'realdepth': realdepth
}
def query(reportclass, request, basequery, sortsql="1 asc"):
basesql, baseparams = basequery.query.get_compiler(basequery.db).as_sql(
with_col_aliases=False
)
# Assure the item hierarchy is up to date
Item.rebuildHierarchy(database=basequery.db)
# Execute a query to get the backlog at the start of the horizon
startbacklogdict = {}
query = """
select name, sum(qty) from
(
select item.name, sum(demand.quantity) qty from (%s) item
inner join item child on child.lft between item.lft and item.rght
inner join demand on demand.item_id = child.name
and demand.status in ('open','quote')
and due < %%s
group by item.name
union all
select item.name, sum(operationplanmaterial.quantity) qty
from (%s) item
inner join item child on child.lft between item.lft and item.rght
inner join operationplanmaterial on operationplanmaterial.item_id = child.name
inner join operationplan on operationplan.reference = operationplanmaterial.operationplan_id
and operationplan.demand_id is not null
and operationplan.enddate < %%s
group by item.name
) t
group by name
""" % (
basesql,
basesql,
)
with transaction.atomic(using=request.database):
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(
query,
baseparams
+ (request.report_startdate,)
+ baseparams
+ (request.report_startdate,),
)
for row in cursor_chunked:
if row[0]:
startbacklogdict[row[0]] = max(float(row[1]), 0)
# Execute the query
query = """
select
parent.name, parent.description, parent.category, parent.subcategory,
parent.owner_id, parent.cost, parent.volume, parent.weight, parent.uom, parent.periodofcover, parent.source, parent.lastmodified,
%s
d.bucket,
d.startdate,
d.enddate,
sum(coalesce((
select sum(quantity)
from demand
inner join item child on child.lft between parent.lft and parent.rght
where demand.item_id = child.name
and status in ('open','quote')
and due >= greatest(%%s,d.startdate)
and due < d.enddate
),0)) orders,
sum(coalesce((
select sum(operationplan.quantity)
from operationplan
inner join item child on child.lft between parent.lft and parent.rght
where operationplan.item_id = child.name
and operationplan.demand_id is not null
and operationplan.enddate >= greatest(%%s,d.startdate)
and operationplan.enddate < d.enddate
),0)) planned,
(select json_agg(json_build_array(f1,f2)) from
(select distinct out_constraint.name as f1, out_constraint.owner as f2
from out_constraint
inner join item child
on child.lft between parent.lft and parent.rght
inner join operationplan
on operationplan.demand_id = out_constraint.demand
and operationplan.due is not null
and out_constraint.item = child.name
and operationplan.enddate >= greatest(%%s,d.startdate)
and (
out_constraint.name not in ('before current', 'before fence')
or out_constraint.enddate > d.enddate
)
and operationplan.due < d.enddate
limit 20
) cte_reasons
) reasons
from (%s) parent
cross join (
select name as bucket, startdate, enddate
from common_bucketdetail
where bucket_id = %%s and enddate > %%s and startdate < %%s
) d
group by
parent.name, parent.description, parent.category, parent.subcategory,
parent.owner_id, parent.cost, parent.volume, parent.weight, parent.uom, parent.periodofcover,
parent.source, parent.lastmodified, parent.lft, parent.rght,
%s
d.bucket, d.startdate, d.enddate
order by %s, d.startdate
""" % (
reportclass.attr_sql,
basesql,
reportclass.attr_sql,
sortsql,
)
# Build the python result
with transaction.atomic(using=request.database):
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(
query,
(request.report_startdate,) * 3 # orders + planned + constraints
+ baseparams # orders planned
+ (
request.report_bucket,
request.report_startdate,
request.report_enddate,
), # buckets
)
previtem = None
itemattributefields = getAttributeFields(Item)
for row in cursor_chunked:
numfields = len(row)
if row[0] != previtem:
backlog = startbacklogdict.get(row[0], 0)
previtem = row[0]
backlog += float(row[numfields - 3]) - float(row[numfields - 2])
res = {
"item": row[0],
"description": row[1],
"category": row[2],
"subcategory": row[3],
"owner": row[4],
"cost": row[5],
"volume": row[6],
"weight": row[7],
"uom": row[8],
"periodofcover": row[9],
"source": row[10],
"lastmodified": row[11],
"bucket": row[numfields - 6],
"startdate": row[numfields - 5].date(),
"enddate": row[numfields - 4].date(),
"demand": row[numfields - 3],
"supply": row[numfields - 2],
"reasons": json.dumps(row[numfields - 1]),
"backlog": max(backlog or 0, 0),
}
idx = 12
for f in itemattributefields:
res[f.field_name] = row[idx]
idx += 1
yield res
def query(request, basequery, sortsql='1 asc'):
basesql, baseparams = basequery.query.get_compiler(
basequery.db).as_sql(with_col_aliases=True)
cursor = connections[request.database].cursor()
# Assure the item hierarchy is up to date
Item.rebuildHierarchy(database=basequery.db)
# Execute a query to get the backlog at the start of the horizon
startbacklogdict = {}
query = '''
select items.name, sum(quantity)
from (%s) items
inner join item
on item.lft between items.lft and items.rght
inner join out_demand
on item.name = out_demand.item
and (plandate is null or plandate >= '%s')
and due < '%s'
group by items.name
''' % (basesql, request.report_startdate, request.report_startdate)
cursor.execute(query, baseparams)
for row in cursor.fetchall():
if row[0]:
startbacklogdict[row[0]] = float(row[1])
# Execute the query
query = '''
select y.name as row1,
y.bucket as col1, y.startdate as col2, y.enddate as col3,
min(y.orders),
min(y.planned)
from (
select x.name as name, x.lft as lft, x.rght as rght,
x.bucket as bucket, x.startdate as startdate, x.enddate as enddate,
coalesce(sum(demand.quantity),0) as orders,
min(x.planned) as planned
from (
select items.name as name, items.lft as lft, items.rght as rght,
d.bucket as bucket, d.startdate as startdate, d.enddate as enddate,
coalesce(sum(out_demand.quantity),0) as planned
from (%s) items
-- Multiply with buckets
cross join (
select name as bucket, startdate, enddate
from common_bucketdetail
where bucket_id = '%s' and enddate > '%s' and startdate < '%s'
) d
-- Include hierarchical children
inner join item
on item.lft between items.lft and items.rght
-- Planned quantity
left join out_demand
on item.name = out_demand.item
and d.startdate <= out_demand.plandate
and d.enddate > out_demand.plandate
and out_demand.plandate >= '%s'
and out_demand.plandate < '%s'
-- Grouping
group by items.name, items.lft, items.rght, d.bucket, d.startdate, d.enddate
) x
-- Requested quantity
inner join item
on item.lft between x.lft and x.rght
left join demand
on item.name = demand.item_id
and x.startdate <= demand.due
and x.enddate > demand.due
and demand.due >= '%s'
and demand.due < '%s'
-- Grouping
group by x.name, x.lft, x.rght, x.bucket, x.startdate, x.enddate
) y
-- Ordering and grouping
group by y.name, y.lft, y.rght, y.bucket, y.startdate, y.enddate
order by %s, y.startdate
''' % (basesql, request.report_bucket, request.report_startdate,
request.report_enddate, request.report_startdate,
request.report_enddate, request.report_startdate,
request.report_enddate, sortsql)
cursor.execute(query, baseparams)
# Build the python result
previtem = None
for row in cursor.fetchall():
if row[0] != previtem:
backlog = startbacklogdict.get(row[0], 0)
previtem = row[0]
backlog += float(row[4]) - float(row[5])
yield {
'item': row[0],
'bucket': row[1],
'startdate': python_date(row[2]),
'enddate': python_date(row[3]),
'demand': round(row[4], 1),
'supply': round(row[5], 1),
'backlog': round(backlog, 1)
}
def query(request, basequery, sortsql='1 asc'):
basesql, baseparams = basequery.query.get_compiler(basequery.db).as_sql(with_col_aliases=False)
cursor = connections[request.database].cursor()
# Assure the item hierarchy is up to date
Item.rebuildHierarchy(database=basequery.db)
# Execute a query to get the backlog at the start of the horizon
startbacklogdict = {}
query = '''
select items.name, sum(quantity)
from (%s) items
inner join item
on item.lft between items.lft and items.rght
inner join out_demand
on item.name = out_demand.item
and (plandate is null or plandate >= '%s')
and due < '%s'
group by items.name
''' % (basesql, request.report_startdate, request.report_startdate)
cursor.execute(query, baseparams)
for row in cursor.fetchall():
if row[0]:
startbacklogdict[row[0]] = float(row[1])
# Execute the query
query = '''
select y.name as row1,
y.bucket as col1, y.startdate as col2, y.enddate as col3,
min(y.orders),
min(y.planned)
from (
select x.name as name, x.lft as lft, x.rght as rght,
x.bucket as bucket, x.startdate as startdate, x.enddate as enddate,
coalesce(sum(demand.quantity),0) as orders,
min(x.planned) as planned
from (
select items.name as name, items.lft as lft, items.rght as rght,
d.bucket as bucket, d.startdate as startdate, d.enddate as enddate,
coalesce(sum(out_demand.quantity),0) as planned
from (%s) items
-- Multiply with buckets
cross join (
select name as bucket, startdate, enddate
from common_bucketdetail
where bucket_id = '%s' and enddate > '%s' and startdate < '%s'
) d
-- Include hierarchical children
inner join item
on item.lft between items.lft and items.rght
-- Planned quantity
left join out_demand
on item.name = out_demand.item
and d.startdate <= out_demand.plandate
and d.enddate > out_demand.plandate
and out_demand.plandate >= '%s'
and out_demand.plandate < '%s'
-- Grouping
group by items.name, items.lft, items.rght, d.bucket, d.startdate, d.enddate
) x
-- Requested quantity
inner join item
on item.lft between x.lft and x.rght
left join demand
on item.name = demand.item_id
and x.startdate <= demand.due
and x.enddate > demand.due
and demand.due >= '%s'
and demand.due < '%s'
and demand.status = 'open'
-- Grouping
group by x.name, x.lft, x.rght, x.bucket, x.startdate, x.enddate
) y
-- Ordering and grouping
group by y.name, y.lft, y.rght, y.bucket, y.startdate, y.enddate
order by %s, y.startdate
''' % (basesql, request.report_bucket, request.report_startdate,
request.report_enddate, request.report_startdate,
request.report_enddate, request.report_startdate,
request.report_enddate, sortsql)
cursor.execute(query, baseparams)
# Build the python result
previtem = None
for row in cursor.fetchall():
if row[0] != previtem:
backlog = startbacklogdict.get(row[0], 0)
previtem = row[0]
backlog += float(row[4]) - float(row[5])
yield {
'item': row[0],
'bucket': row[1],
'startdate': python_date(row[2]),
'enddate': python_date(row[3]),
'demand': round(row[4], 1),
'supply': round(row[5], 1),
'backlog': round(backlog, 1)
}
def query(reportclass, request, basequery, sortsql='1 asc'):
basesql, baseparams = basequery.query.get_compiler(
basequery.db).as_sql(with_col_aliases=False)
# Assure the item hierarchy is up to date
Item.rebuildHierarchy(database=basequery.db)
# Execute a query to get the backlog at the start of the horizon
startbacklogdict = {}
query = '''
select name, sum(qty) from
(
select parent.name, sum(demand.quantity) qty from (%s) item
inner join item parent on item.lft between parent.lft and parent.rght
inner join demand on demand.item_id = item.name and demand.status in ('open','quote') and due < %%s
group by parent.name
union all
select parent.name, sum(operationplanmaterial.quantity) qty
from operationplanmaterial
inner join operationplan on operationplan.reference = operationplanmaterial.operationplan_id
and operationplan.type = 'DLVR'
and operationplan.enddate < %%s
inner join (%s) item on operationplanmaterial.item_id = item.name
inner join item parent on item.lft between parent.lft and parent.rght
group by parent.name
) t
group by name
''' % (basesql, basesql)
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(
query, baseparams +
(request.report_startdate, request.report_startdate) +
baseparams)
for row in cursor_chunked:
if row[0]:
startbacklogdict[row[0]] = float(row[1])
# Execute the query
query = '''
select
parent.name, parent.description, parent.category, parent.subcategory,
parent.owner_id, parent.cost, parent.source, parent.lastmodified,
%s
d.bucket,
d.startdate,
d.enddate,
sum(coalesce((select sum(quantity) from demand
where demand.item_id = child.name and status in ('open','quote') and due >= greatest(%%s,d.startdate) and due < d.enddate),0)) orders,
sum(coalesce((select sum(-operationplanmaterial.quantity) from operationplanmaterial
inner join operationplan on operationplan.reference = operationplanmaterial.operationplan_id and operationplan.type = 'DLVR'
where operationplanmaterial.item_id = child.name
and operationplanmaterial.flowdate >= greatest(%%s,d.startdate)
and operationplanmaterial.flowdate < d.enddate),0)) planned
from (%s) parent
inner join item child on child.lft between parent.lft and parent.rght
cross join (
select name as bucket, startdate, enddate
from common_bucketdetail
where bucket_id = %%s and enddate > %%s and startdate < %%s
) d
group by
parent.name, parent.description, parent.category, parent.subcategory,
parent.owner_id, parent.cost, parent.source, parent.lastmodified,
%s
d.bucket, d.startdate, d.enddate
order by %s, d.startdate
''' % (reportclass.attr_sql, basesql, reportclass.attr_sql, sortsql)
# Build the python result
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(
query,
(request.report_startdate, request.report_startdate)
+ # orders planned
baseparams + (request.report_bucket, request.report_startdate,
request.report_enddate) # buckets
)
previtem = None
for row in cursor_chunked:
numfields = len(row)
if row[0] != previtem:
backlog = startbacklogdict.get(row[0], 0)
previtem = row[0]
backlog += float(row[numfields - 2]) - float(
row[numfields - 1])
res = {
'item': row[0],
'description': row[1],
'category': row[2],
'subcategory': row[3],
'owner': row[4],
'cost': row[5],
'source': row[6],
'lastmodified': row[7],
'bucket': row[numfields - 5],
'startdate': row[numfields - 4].date(),
'enddate': row[numfields - 3].date(),
'demand': row[numfields - 2],
'supply': row[numfields - 1],
'backlog': backlog,
}
idx = 8
for f in getAttributeFields(Item):
res[f.field_name] = row[idx]
idx += 1
yield res
def query(reportclass, request, basequery, sortsql='1 asc'):
basesql, baseparams = basequery.query.get_compiler(
basequery.db).as_sql(with_col_aliases=False)
# Assure the item hierarchy is up to date
Item.rebuildHierarchy(database=basequery.db)
# Execute a query to get the backlog at the start of the horizon
startbacklogdict = {}
query = '''
select items.name, coalesce(req.qty, 0) - coalesce(pln.qty, 0)
from (%s) items
left outer join (
select parent.name, sum(quantity) qty
from demand
inner join item on demand.item_id = item.name
inner join item parent on item.lft between parent.lft and parent.rght
where status in ('open', 'quote')
and due < %%s
group by parent.name
) req
on req.name = items.name
left outer join (
select parent.name, sum(operationplan.quantity) qty
from operationplan
inner join demand on operationplan.demand_id = demand.name
and operationplan.owner_id is null
and operationplan.enddate < %%s
inner join item on demand.item_id = item.name
inner join item parent on item.lft between parent.lft and parent.rght
group by parent.name
) pln
on pln.name = items.name
''' % basesql
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(
query, baseparams +
(request.report_startdate, request.report_startdate))
for row in cursor_chunked:
if row[0]:
startbacklogdict[row[0]] = float(row[1])
# Execute the query
query = '''
select
parent.name, %s
d.bucket,
d.startdate,
d.enddate,
sum(coalesce((select sum(quantity) from demand
where demand.item_id = child.name and status in ('open','quote') and due >= greatest(%%s,d.startdate) and due < d.enddate),0)) orders,
sum(coalesce((select sum(-operationplanmaterial.quantity) from operationplanmaterial
inner join operationplan on operationplan.id = operationplanmaterial.operationplan_id and operationplan.type = 'DLVR'
where operationplanmaterial.item_id = child.name
and operationplanmaterial.flowdate >= greatest(%%s,d.startdate)
and operationplanmaterial.flowdate < d.enddate),0)) planned
from (%s) parent
inner join item child on child.lft between parent.lft and parent.rght
cross join (
select name as bucket, startdate, enddate
from common_bucketdetail
where bucket_id = %%s and enddate > %%s and startdate < %%s
) d
group by
parent.name, %s
d.bucket,
d.startdate,
d.enddate
order by %s, d.startdate
''' % (reportclass.attr_sql, basesql, reportclass.attr_sql, sortsql)
# Build the python result
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(
query,
baseparams + (
request.report_startdate, #orders
request.report_startdate, #planned
request.report_bucket,
request.report_startdate,
request.report_enddate #buckets
))
previtem = None
for row in cursor_chunked:
numfields = len(row)
if row[0] != previtem:
backlog = startbacklogdict.get(row[0], 0)
previtem = row[0]
backlog += float(row[numfields - 2]) - float(
row[numfields - 1])
res = {
'item': row[0],
'bucket': row[numfields - 5],
'startdate': row[numfields - 4].date(),
'enddate': row[numfields - 3].date(),
'demand': round(row[numfields - 2], 1),
'supply': round(row[numfields - 1], 1),
'backlog': round(backlog, 1),
}
idx = 1
for f in getAttributeFields(Item):
res[f.field_name] = row[idx]
idx += 1
yield res
def query(reportclass, request, basequery, sortsql='1 asc'):
basesql, baseparams = basequery.query.get_compiler(
basequery.db).as_sql(with_col_aliases=False)
# Assure the item hierarchy is up to date
Item.rebuildHierarchy(database=basequery.db)
# Execute a query to get the backlog at the start of the horizon
startbacklogdict = {}
query = '''
select items.name, coalesce(req.qty, 0) - coalesce(pln.qty, 0)
from (%s) items
left outer join (
select parent.name, sum(quantity) qty
from demand
inner join item on demand.item_id = item.name
inner join item parent on item.lft between parent.lft and parent.rght
where status in ('open', 'quote')
and due < %%s
group by parent.name
) req
on req.name = items.name
left outer join (
select parent.name, sum(operationplan.quantity) qty
from operationplan
inner join demand on operationplan.demand_id = demand.name
and operationplan.owner_id is null
and operationplan.enddate < %%s
inner join item on demand.item_id = item.name
inner join item parent on item.lft between parent.lft and parent.rght
group by parent.name
) pln
on pln.name = items.name
''' % basesql
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(
query, baseparams +
(request.report_startdate, request.report_startdate))
for row in cursor_chunked:
if row[0]:
startbacklogdict[row[0]] = float(row[1])
# Execute the query
query = '''
select y.name, %s
y.bucket, y.startdate, y.enddate,
min(y.orders),
min(y.planned)
from (
select x.name as name, x.lft as lft, x.rght as rght,
x.bucket as bucket, x.startdate as startdate, x.enddate as enddate,
coalesce(sum(demand.quantity),0) as orders,
min(x.planned) as planned
from (
select items.name as name, items.lft as lft, items.rght as rght,
d.bucket as bucket, d.startdate as startdate, d.enddate as enddate,
coalesce(sum(operationplan.quantity),0) as planned
from (%s) items
-- Multiply with buckets
cross join (
select name as bucket, startdate, enddate
from common_bucketdetail
where bucket_id = %%s and enddate > %%s and startdate < %%s
) d
-- Include hierarchical children
inner join item
on item.lft between items.lft and items.rght
-- Planned quantity
left outer join operationplan
on operationplan.type = 'DLVR'
and operationplan.item_id = item.name
and d.startdate <= operationplan.enddate
and d.enddate > operationplan.enddate
and operationplan.enddate >= %%s
and operationplan.enddate < %%s
-- Grouping
group by items.name, items.lft, items.rght, d.bucket, d.startdate, d.enddate
) x
-- Requested quantity
inner join item
on item.lft between x.lft and x.rght
left join demand
on item.name = demand.item_id
and x.startdate <= demand.due
and x.enddate > demand.due
and demand.due >= %%s
and demand.due < %%s
and demand.status in ('open', 'quote')
-- Grouping
group by x.name, x.lft, x.rght, x.bucket, x.startdate, x.enddate
) y
-- Ordering and grouping
group by %s y.name, y.lft, y.rght, y.bucket, y.startdate, y.enddate
order by %s, y.startdate
''' % (reportclass.attr_sql, basesql, reportclass.attr_sql, sortsql)
# Build the python result
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(
query,
baseparams + (request.report_bucket, request.report_startdate,
request.report_enddate, request.report_startdate,
request.report_enddate, request.report_startdate,
request.report_enddate))
previtem = None
for row in cursor_chunked:
numfields = len(row)
if row[0] != previtem:
backlog = startbacklogdict.get(row[0], 0)
previtem = row[0]
backlog += float(row[numfields - 2]) - float(
row[numfields - 1])
res = {
'item': row[0],
'bucket': row[numfields - 5],
'startdate': row[numfields - 4].date(),
'enddate': row[numfields - 3].date(),
'demand': round(row[numfields - 2], 1),
'supply': round(row[numfields - 1], 1),
'backlog': round(backlog, 1),
}
idx = 1
for f in getAttributeFields(Item):
res[f.field_name] = row[idx]
idx += 1
yield res
def run(cls, database=DEFAULT_DB_ALIAS, **kwargs):
import frepple
with connections[database].cursor() as cursor:
# Update item metrics
try:
try:
window = frepple.settings.current + timedelta(days=int(
Parameter.getValue("metrics.demand_window", database,
"999")))
except Exception:
print("Warning: invalid parameter 'metrics.demand_window'")
window = datetime(2030, 12, 31)
Item.createRootObject(database=database)
cursor.execute(
"""
create temporary table item_hierarchy (parent character varying(300),
child character varying(300));
insert into item_hierarchy
select parent.name, item.name from item
inner join item parent on item.lft > parent.lft and item.lft < parent.rght;
create index on item_hierarchy (child);
create temporary table out_problem_tmp
as
select item.name as item_id, out_problem.name, out_problem.weight, out_problem.weight*coalesce(item.cost,0) as weight_cost
from out_problem
inner join demand on demand.name = out_problem.owner
inner join item on item.name = demand.item_id
where out_problem.name in ('unplanned', 'late')
and out_problem.startdate < %s;
""",
(window, ),
)
cursor.execute("""
create temporary table metrics as
select item.name as item_id,
coalesce(sum(case when out_problem_tmp.name = 'late' then 1 end),0) as latedemandcount,
coalesce(sum(case when out_problem_tmp.name = 'late' then out_problem_tmp.weight end),0) as latedemandquantity,
coalesce(sum(case when out_problem_tmp.name = 'late' then out_problem_tmp.weight_cost end),0) as latedemandvalue,
coalesce(sum(case when out_problem_tmp.name = 'unplanned' then 1 end),0) as unplanneddemandcount,
coalesce(sum(case when out_problem_tmp.name = 'unplanned' then out_problem_tmp.weight end),0) as unplanneddemandquantity,
coalesce(sum(case when out_problem_tmp.name = 'unplanned' then out_problem_tmp.weight_cost end),0) as unplanneddemandvalue
from item
left outer join out_problem_tmp on out_problem_tmp.item_id = item.name
where item.lft = item.rght - 1
group by item.name;
create unique index on metrics (item_id);
insert into metrics
select parent,
coalesce(sum(latedemandcount),0),
coalesce(sum(latedemandquantity),0),
coalesce(sum(latedemandvalue),0),
coalesce(sum(unplanneddemandcount),0),
coalesce(sum(unplanneddemandquantity),0),
coalesce(sum(unplanneddemandvalue),0)
from item_hierarchy
left outer join metrics on item_hierarchy.child = metrics.item_id
group by parent;
""")
cursor.execute("""
update item
set latedemandcount = metrics.latedemandcount,
latedemandquantity = metrics.latedemandquantity,
latedemandvalue = metrics.latedemandvalue,
unplanneddemandcount = metrics.unplanneddemandcount,
unplanneddemandquantity = metrics.unplanneddemandquantity,
unplanneddemandvalue = metrics.unplanneddemandvalue
from metrics
where item.name = metrics.item_id
and (item.latedemandcount is distinct from metrics.latedemandcount
or item.latedemandquantity is distinct from metrics.latedemandquantity
or item.latedemandvalue is distinct from metrics.latedemandvalue
or item.unplanneddemandcount is distinct from metrics.unplanneddemandcount
or item.unplanneddemandquantity is distinct from metrics.unplanneddemandquantity
or item.unplanneddemandvalue is distinct from metrics.unplanneddemandvalue);
""")
cursor.execute("""
drop table item_hierarchy;
drop table out_problem_tmp;
drop table metrics;
""")
except Exception as e:
print("Error updating item metrics: %s" % e)
# Update resource metrics
try:
Resource.rebuildHierarchy(database)
cursor.execute("""
with resource_hierarchy as (select child.name child, parent.name parent
from resource child
inner join resource parent on child.lft between parent.lft and parent.rght
where child.lft = child.rght-1),
cte as (
select parent, count(out_problem.id) as overloadcount from resource_hierarchy
left outer join out_problem
on out_problem.name = 'overload'
and out_problem.owner = resource_hierarchy.child
group by parent
)
update resource
set overloadcount = cte.overloadcount
from cte
where cte.parent = resource.name
and resource.overloadcount is distinct from cte.overloadcount;
""")
except Exception as e:
print("Error updating resource metrics: %s" % e)
def basequeryset(reportclass, request, *args, **kwargs):
q = PurchaseOrder.objects.all()
if "calendarstart" in request.GET:
q = q.filter(
Q(enddate__gte=request.GET["calendarstart"])
| (
Q(enddate__isnull=True)
& Q(startdate__gte=request.GET["calendarstart"])
)
)
if "calendarend" in request.GET:
q = q.filter(
Q(startdate__lte=request.GET["calendarend"])
| (
Q(startdate__isnull=True)
& Q(enddate__lte=request.GET["calendarend"])
)
)
if args and args[0]:
paths = request.path.split("/")
path = paths[4]
if paths[4] == "operationplanmaterial":
q = q.filter(
location__name=args[1],
item__name=args[0],
startdate__lt=args[2],
enddate__gte=args[2],
)
elif path == "produced":
q = q.filter(
location__name=args[1],
item__name=args[0],
enddate__gte=args[2],
enddate__lt=args[3],
)
elif path == "supplier" or request.path.startswith(
"/detail/input/supplier/"
):
try:
Supplier.rebuildHierarchy(database=request.database)
sup = (
Supplier.objects.all().using(request.database).get(name=args[0])
)
lft = sup.lft
rght = sup.rght
except Supplier.DoesNotExist:
lft = 1
rght = 1
q = q.filter(supplier__lft__gte=lft, supplier__rght__lte=rght)
elif path == "location" or request.path.startswith(
"/detail/input/location/"
):
try:
Location.rebuildHierarchy(database=request.database)
loc = (
Location.objects.all().using(request.database).get(name=args[0])
)
lft = loc.lft
rght = loc.rght
except Location.DoesNotExist:
lft = 1
rght = 1
q = q.filter(location__lft__gte=lft, location__rght__lte=rght)
elif path == "item" or request.path.startswith("/detail/input/item/"):
try:
Item.rebuildHierarchy(database=request.database)
itm = Item.objects.all().using(request.database).get(name=args[0])
lft = itm.lft
rght = itm.rght
except Item.DoesNotExist:
lft = 1
rght = 1
q = q.filter(item__lft__gte=lft, item__rght__lte=rght)
q = reportclass.operationplanExtraBasequery(q.select_related("item"), request)
return q.annotate(
unit_cost=Cast(
RawSQL(
"""
coalesce((
select cost
from itemsupplier
where itemsupplier.item_id = operationplan.item_id
and (itemsupplier.location_id is null or itemsupplier.location_id = operationplan.location_id)
and itemsupplier.supplier_id = operationplan.supplier_id
order by operationplan.enddate < itemsupplier.effective_end desc nulls first,
operationplan.enddate >= itemsupplier.effective_start desc nulls first,
priority <> 0,
priority
limit 1),
(select cost from item where item.name = operationplan.item_id), 0)
""",
[],
),
output_field=FloatField(),
),
total_cost=Cast(F("unit_cost") * F("quantity"), output_field=FloatField()),
total_volume=Cast(
F("item__volume") * F("quantity"), output_field=FloatField()
),
total_weight=Cast(
F("item__weight") * F("quantity"), output_field=FloatField()
),
feasible=RawSQL(
"coalesce((operationplan.plan->>'feasible')::boolean, true)", []
),
computed_color=RawSQL(
"""
case when operationplan.color >= 999999 and operationplan.plan ? 'item' then
999999
- extract(epoch from operationplan.delay)/86400.0
+ 1000000
when operationplan.color >= 999999 and not(operationplan.plan ? 'item') then
999999
- extract(epoch from operationplan.delay)/86400.0
else operationplan.color
end
""",
[],
),
itemsupplier_sizeminimum=Cast(
RawSQL(
"""
select sizeminimum
from itemsupplier
where itemsupplier.item_id = operationplan.item_id
and (itemsupplier.location_id is null or itemsupplier.location_id = operationplan.location_id)
and itemsupplier.supplier_id = operationplan.supplier_id
order by operationplan.enddate < itemsupplier.effective_end desc nulls first,
operationplan.enddate >= itemsupplier.effective_start desc nulls first,
priority <> 0,
priority
limit 1
""",
[],
),
output_field=FloatField(),
),
itemsupplier_sizemultiple=Cast(
RawSQL(
"""
select sizemultiple
from itemsupplier
where itemsupplier.item_id = operationplan.item_id
and (itemsupplier.location_id is null or itemsupplier.location_id = operationplan.location_id)
and itemsupplier.supplier_id = operationplan.supplier_id
order by operationplan.enddate < itemsupplier.effective_end desc nulls first,
operationplan.enddate >= itemsupplier.effective_start desc nulls first,
priority <> 0,
priority
limit 1
""",
[],
),
output_field=FloatField(),
),
itemsupplier_sizemaximum=Cast(
RawSQL(
"""
select sizemaximum
from itemsupplier
where itemsupplier.item_id = operationplan.item_id
and (itemsupplier.location_id is null or itemsupplier.location_id = operationplan.location_id)
and itemsupplier.supplier_id = operationplan.supplier_id
order by operationplan.enddate < itemsupplier.effective_end desc nulls first,
operationplan.enddate >= itemsupplier.effective_start desc nulls first,
priority <> 0,
priority
limit 1
""",
[],
),
output_field=FloatField(),
),
itemsupplier_priority=Cast(
RawSQL(
"""
select priority
from itemsupplier
where itemsupplier.item_id = operationplan.item_id
and (itemsupplier.location_id is null or itemsupplier.location_id = operationplan.location_id)
and itemsupplier.supplier_id = operationplan.supplier_id
order by operationplan.enddate < itemsupplier.effective_end desc nulls first,
operationplan.enddate >= itemsupplier.effective_start desc nulls first,
priority <> 0,
priority
limit 1
""",
[],
),
output_field=FloatField(),
),
itemsupplier_effective_start=Cast(
RawSQL(
"""
select effective_start
from itemsupplier
where itemsupplier.item_id = operationplan.item_id
and (itemsupplier.location_id is null or itemsupplier.location_id = operationplan.location_id)
and itemsupplier.supplier_id = operationplan.supplier_id
order by operationplan.enddate < itemsupplier.effective_end desc nulls first,
operationplan.enddate >= itemsupplier.effective_start desc nulls first,
priority <> 0,
priority
limit 1
""",
[],
),
output_field=DateTimeField(),
),
itemsupplier_effective_end=Cast(
RawSQL(
"""
select effective_end
from itemsupplier
where itemsupplier.item_id = operationplan.item_id
and (itemsupplier.location_id is null or itemsupplier.location_id = operationplan.location_id)
and itemsupplier.supplier_id = operationplan.supplier_id
order by operationplan.enddate < itemsupplier.effective_end desc nulls first,
operationplan.enddate >= itemsupplier.effective_start desc nulls first,
priority <> 0,
priority
limit 1
""",
[],
),
output_field=DateTimeField(),
),
)
def query(reportclass, request, basequery, sortsql='1 asc'):
basesql, baseparams = basequery.query.get_compiler(basequery.db).as_sql(with_col_aliases=False)
# Assure the item hierarchy is up to date
Item.rebuildHierarchy(database=basequery.db)
# Execute a query to get the backlog at the start of the horizon
startbacklogdict = {}
query = '''
select name, sum(qty) from
(
select parent.name, sum(demand.quantity) qty from (%s) item
inner join item parent on item.lft between parent.lft and parent.rght
inner join demand on demand.item_id = item.name and demand.status in ('open','quote') and due < %%s
group by parent.name
union all
select parent.name, sum(operationplanmaterial.quantity) qty
from operationplanmaterial
inner join operationplan on operationplan.reference = operationplanmaterial.operationplan_id
and operationplan.type = 'DLVR'
and operationplan.enddate < %%s
inner join (%s) item on operationplanmaterial.item_id = item.name
inner join item parent on item.lft between parent.lft and parent.rght
group by parent.name
) t
group by name
''' % (basesql, basesql)
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(query, baseparams + (request.report_startdate, request.report_startdate) + baseparams)
for row in cursor_chunked:
if row[0]:
startbacklogdict[row[0]] = float(row[1])
# Execute the query
query = '''
select
parent.name, parent.description, parent.category, parent.subcategory,
parent.owner_id, parent.cost, parent.source, parent.lastmodified,
%s
d.bucket,
d.startdate,
d.enddate,
sum(coalesce((select sum(quantity) from demand
where demand.item_id = child.name and status in ('open','quote') and due >= greatest(%%s,d.startdate) and due < d.enddate),0)) orders,
sum(coalesce((select sum(-operationplanmaterial.quantity) from operationplanmaterial
inner join operationplan on operationplan.reference = operationplanmaterial.operationplan_id and operationplan.type = 'DLVR'
where operationplanmaterial.item_id = child.name
and operationplanmaterial.flowdate >= greatest(%%s,d.startdate)
and operationplanmaterial.flowdate < d.enddate),0)) planned
from (%s) parent
inner join item child on child.lft between parent.lft and parent.rght
cross join (
select name as bucket, startdate, enddate
from common_bucketdetail
where bucket_id = %%s and enddate > %%s and startdate < %%s
) d
group by
parent.name, parent.description, parent.category, parent.subcategory,
parent.owner_id, parent.cost, parent.source, parent.lastmodified,
%s
d.bucket, d.startdate, d.enddate
order by %s, d.startdate
''' % (reportclass.attr_sql, basesql, reportclass.attr_sql, sortsql)
# Build the python result
with connections[request.database].chunked_cursor() as cursor_chunked:
cursor_chunked.execute(
query,
(request.report_startdate, request.report_startdate) + # orders planned
baseparams +
(request.report_bucket, request.report_startdate, request.report_enddate) # buckets
)
previtem = None
for row in cursor_chunked:
numfields = len(row)
if row[0] != previtem:
backlog = startbacklogdict.get(row[0], 0)
previtem = row[0]
backlog += float(row[numfields - 2]) - float(row[numfields - 1])
res = {
'item': row[0],
'description': row[1],
'category': row[2],
'subcategory': row[3],
'owner': row[4],
'cost': row[5],
'source': row[6],
'lastmodified': row[7],
'bucket': row[numfields - 5],
'startdate': row[numfields - 4].date(),
'enddate': row[numfields - 3].date(),
'demand': row[numfields - 2],
'supply': row[numfields - 1],
'backlog': backlog,
}
idx = 8
for f in getAttributeFields(Item):
res[f.field_name] = row[idx]
idx += 1
yield res
