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 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