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