Ejemplo n.º 1
0
    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", []))
Ejemplo n.º 2
0
  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
        }
Ejemplo n.º 3
0
    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
Ejemplo n.º 4
0
    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)
            }
Ejemplo n.º 5
0
  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)
        }
Ejemplo n.º 6
0
    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
Ejemplo n.º 7
0
    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
Ejemplo n.º 8
0
    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
Ejemplo n.º 9
0
    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)
Ejemplo n.º 10
0
    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(),
            ),
        )
Ejemplo n.º 11
0
  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