Exemplo n.º 1
0
  def query(request, basequery, sortsql='1 asc'):
    cursor = connections[request.database].cursor()
    basesql, baseparams = basequery.query.get_compiler(basequery.db).as_sql(with_col_aliases=False)

    # Assure the item hierarchy is up to date
    Buffer.rebuildHierarchy(database=basequery.db)

    # Execute a query  to get the onhand value at the start of our horizon
    startohdict = {}
    query = '''
      select buffers.name, sum(oh.onhand)
      from (%s) buffers
      inner join buffer
      on buffer.lft between buffers.lft and buffers.rght
      inner join (
      select out_flowplan.thebuffer as thebuffer, out_flowplan.onhand as onhand
      from out_flowplan,
        (select thebuffer, max(id) as id
         from out_flowplan
         where flowdate < '%s'
         group by thebuffer
        ) maxid
      where maxid.thebuffer = out_flowplan.thebuffer
      and maxid.id = out_flowplan.id
      ) oh
      on oh.thebuffer = buffer.name
      group by buffers.name
      ''' % (basesql, request.report_startdate)
    cursor.execute(query, baseparams)
    for row in cursor.fetchall():
      startohdict[row[0]] = float(row[1])

    # Execute the actual query
    query = '''
      select buf.name as row1, buf.item_id as row2, buf.location_id as row3,
             d.bucket as col1, d.startdate as col2, d.enddate as col3,
             coalesce(sum(%s),0.0) as consumed,
             coalesce(-sum(%s),0.0) as produced
        from (%s) buf
        -- 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 child buffers
        inner join buffer
        on buffer.lft between buf.lft and buf.rght
        -- Consumed and produced quantities
        left join out_flowplan
        on buffer.name = out_flowplan.thebuffer
        and d.startdate <= out_flowplan.flowdate
        and d.enddate > out_flowplan.flowdate
        and out_flowplan.flowdate >= '%s'
        and out_flowplan.flowdate < '%s'
        -- Grouping and sorting
        group by buf.name, buf.item_id, buf.location_id, buf.onhand, d.bucket, d.startdate, d.enddate
        order by %s, d.startdate
      ''' % (
        sql_max('out_flowplan.quantity', '0.0'), sql_min('out_flowplan.quantity', '0.0'),
        basesql, request.report_bucket, request.report_startdate, request.report_enddate,
        request.report_startdate, request.report_enddate, sortsql
      )
    cursor.execute(query, baseparams)

    # Build the python result
    prevbuf = None
    for row in cursor.fetchall():
      if row[0] != prevbuf:
        prevbuf = row[0]
        startoh = startohdict.get(prevbuf, 0)
        endoh = startoh + float(row[6] - row[7])
      else:
        startoh = endoh
        endoh += float(row[6] - row[7])
      yield {
        'buffer': row[0],
        'item': row[1],
        'location': row[2],
        'bucket': row[3],
        'startdate': python_date(row[4]),
        'enddate': python_date(row[5]),
        'startoh': round(startoh, 1),
        'produced': round(row[6], 1),
        'consumed': round(row[7], 1),
        'endoh': round(endoh, 1),
        }
Exemplo n.º 2
0
    def query(reportclass, request, basequery, sortsql='1 asc'):
        cursor = connections[request.database].cursor()
        basesql, baseparams = basequery.query.get_compiler(
            basequery.db).as_sql(with_col_aliases=False)

        # Assure the item hierarchy is up to date
        Buffer.rebuildHierarchy(database=basequery.db)

        # Execute a query  to get the onhand value at the start of our horizon
        startohdict = {}
        query = '''
      select buffers.name, sum(oh.onhand)
      from (%s) buffers
      inner join buffer
      on buffer.lft between buffers.lft and buffers.rght
      inner join (
      select operationplanmaterial.buffer as buffer, operationplanmaterial.onhand as onhand
      from operationplanmaterial,
        (select buffer, max(id) as id
         from operationplanmaterial
         where flowdate < '%s'
         group by buffer
        ) maxid
      where maxid.buffer = operationplanmaterial.buffer
      and maxid.id = operationplanmaterial.id
      ) oh
      on oh.buffer = buffer.name
      group by buffers.name
      ''' % (basesql, request.report_startdate)
        cursor.execute(query, baseparams)
        for row in cursor.fetchall():
            startohdict[row[0]] = float(row[1])

        # Execute the actual query
        query = '''
      select
        invplan.buffer_id, item.name, location.name, %s
        invplan.bucket, invplan.startdate, invplan.enddate,
        invplan.consumed, invplan.produced
      from (
        select
          buf.name as buffer_id,
          d.bucket as bucket, d.startdate as startdate, d.enddate as enddate,
          coalesce(sum(greatest(operationplanmaterial.quantity, 0)),0) as consumed,
          coalesce(-sum(least(operationplanmaterial.quantity, 0)),0) as produced
        from (%s) buf
        -- 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 child buffers
        inner join buffer
        on buffer.lft between buf.lft and buf.rght
        -- Consumed and produced quantities
        left join operationplanmaterial
        on buffer.name = operationplanmaterial.buffer
        and d.startdate <= operationplanmaterial.flowdate
        and d.enddate > operationplanmaterial.flowdate
        and operationplanmaterial.flowdate >= %%s
        and operationplanmaterial.flowdate < %%s
        -- Grouping and sorting
        group by buf.name, buf.item_id, buf.location_id, buf.onhand, d.bucket, d.startdate, d.enddate
        ) invplan
      left outer join buffer on
        invplan.buffer_id = buffer.name
      left outer join item on
        buffer.item_id = item.name
      left outer join location on
        buffer.location_id = location.name
      order by %s, invplan.startdate
      ''' % (reportclass.attr_sql, basesql, sortsql)
        cursor.execute(
            query,
            baseparams + (request.report_bucket, request.report_startdate,
                          request.report_enddate, request.report_startdate,
                          request.report_enddate))

        # Build the python result
        prevbuf = None
        for row in cursor.fetchall():
            numfields = len(row)
            if row[0] != prevbuf:
                prevbuf = row[0]
                startoh = startohdict.get(prevbuf, 0)
                endoh = startoh + float(row[numfields - 2] -
                                        row[numfields - 1])
            else:
                startoh = endoh
                endoh += float(row[numfields - 2] - row[numfields - 1])
            res = {
                'buffer': row[0],
                'item': row[1],
                'location': row[2],
                'bucket': row[numfields - 5],
                'startdate': row[numfields - 4].date(),
                'enddate': row[numfields - 3].date(),
                'startoh': round(startoh, 1),
                'produced': round(row[numfields - 2], 1),
                'consumed': round(row[numfields - 1], 1),
                'endoh': round(endoh, 1),
            }
            # Add attribute fields
            idx = 3
            for f in getAttributeFields(Item, related_name_prefix="item"):
                res[f.field_name] = row[idx]
                idx += 1
            for f in getAttributeFields(Location,
                                        related_name_prefix="location"):
                res[f.field_name] = row[idx]
                idx += 1
            yield res
Exemplo n.º 3
0
    def query(request, basequery, sortsql='1 asc'):
        cursor = connections[request.database].cursor()
        basesql, baseparams = basequery.query.get_compiler(
            basequery.db).as_sql(with_col_aliases=False)

        # Assure the item hierarchy is up to date
        Buffer.rebuildHierarchy(database=basequery.db)

        # Execute a query  to get the onhand value at the start of our horizon
        startohdict = {}
        query = '''
      select buffers.name, sum(oh.onhand)
      from (%s) buffers
      inner join buffer
      on buffer.lft between buffers.lft and buffers.rght
      inner join (
      select out_flowplan.thebuffer as thebuffer, out_flowplan.onhand as onhand
      from out_flowplan,
        (select thebuffer, max(id) as id
         from out_flowplan
         where flowdate < '%s'
         group by thebuffer
        ) maxid
      where maxid.thebuffer = out_flowplan.thebuffer
      and maxid.id = out_flowplan.id
      ) oh
      on oh.thebuffer = buffer.name
      group by buffers.name
      ''' % (basesql, request.report_startdate)
        cursor.execute(query, baseparams)
        for row in cursor.fetchall():
            startohdict[row[0]] = float(row[1])

        # Execute the actual query
        query = '''
      select buf.name as row1, buf.item_id as row2, buf.location_id as row3,
             d.bucket as col1, d.startdate as col2, d.enddate as col3,
             coalesce(sum(%s),0.0) as consumed,
             coalesce(-sum(%s),0.0) as produced
        from (%s) buf
        -- 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 child buffers
        inner join buffer
        on buffer.lft between buf.lft and buf.rght
        -- Consumed and produced quantities
        left join out_flowplan
        on buffer.name = out_flowplan.thebuffer
        and d.startdate <= out_flowplan.flowdate
        and d.enddate > out_flowplan.flowdate
        and out_flowplan.flowdate >= '%s'
        and out_flowplan.flowdate < '%s'
        -- Grouping and sorting
        group by buf.name, buf.item_id, buf.location_id, buf.onhand, d.bucket, d.startdate, d.enddate
        order by %s, d.startdate
      ''' % (sql_max('out_flowplan.quantity',
                     '0.0'), sql_min('out_flowplan.quantity',
                                     '0.0'), basesql, request.report_bucket,
             request.report_startdate, request.report_enddate,
             request.report_startdate, request.report_enddate, sortsql)
        cursor.execute(query, baseparams)

        # Build the python result
        prevbuf = None
        for row in cursor.fetchall():
            if row[0] != prevbuf:
                prevbuf = row[0]
                startoh = startohdict.get(prevbuf, 0)
                endoh = startoh + float(row[6] - row[7])
            else:
                startoh = endoh
                endoh += float(row[6] - row[7])
            yield {
                'buffer': row[0],
                'item': row[1],
                'location': row[2],
                'bucket': row[3],
                'startdate': python_date(row[4]),
                'enddate': python_date(row[5]),
                'startoh': round(startoh, 1),
                'produced': round(row[6], 1),
                'consumed': round(row[7], 1),
                'endoh': round(endoh, 1),
            }
Exemplo n.º 4
0
    def query(reportclass, request, basequery, sortsql="1 asc"):
        cursor = connections[request.database].cursor()
        basesql, baseparams = basequery.query.get_compiler(basequery.db).as_sql(with_col_aliases=False)

        # Assure the item hierarchy is up to date
        Buffer.rebuildHierarchy(database=basequery.db)

        # Execute a query  to get the onhand value at the start of our horizon
        startohdict = {}
        query = """
      select buffers.name, sum(oh.onhand)
      from (%s) buffers
      inner join buffer
      on buffer.lft between buffers.lft and buffers.rght
      inner join (
      select operationplanmaterial.buffer as buffer, operationplanmaterial.onhand as onhand
      from operationplanmaterial,
        (select buffer, max(id) as id
         from operationplanmaterial
         where flowdate < '%s'
         group by buffer
        ) maxid
      where maxid.buffer = operationplanmaterial.buffer
      and maxid.id = operationplanmaterial.id
      ) oh
      on oh.buffer = buffer.name
      group by buffers.name
      """ % (
            basesql,
            request.report_startdate,
        )
        cursor.execute(query, baseparams)
        for row in cursor.fetchall():
            startohdict[row[0]] = float(row[1])

        # Execute the actual query
        query = """
      select buf.name as row1, buf.item_id as row2, buf.location_id as row3,
             d.bucket as col1, d.startdate as col2, d.enddate as col3,
             coalesce(sum(greatest(operationplanmaterial.quantity, 0)),0) as consumed,
             coalesce(-sum(least(operationplanmaterial.quantity, 0)),0) as produced
        from (%s) buf
        -- 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 child buffers
        inner join buffer
        on buffer.lft between buf.lft and buf.rght
        -- Consumed and produced quantities
        left join operationplanmaterial
        on buffer.name = operationplanmaterial.buffer
        and d.startdate <= operationplanmaterial.flowdate
        and d.enddate > operationplanmaterial.flowdate
        and operationplanmaterial.flowdate >= %%s
        and operationplanmaterial.flowdate < %%s
        -- Grouping and sorting
        group by buf.name, buf.item_id, buf.location_id, buf.onhand, d.bucket, d.startdate, d.enddate
        order by %s, d.startdate
      """ % (
            basesql,
            sortsql,
        )
        cursor.execute(
            query,
            baseparams
            + (
                request.report_bucket,
                request.report_startdate,
                request.report_enddate,
                request.report_startdate,
                request.report_enddate,
            ),
        )

        # Build the python result
        prevbuf = None
        for row in cursor.fetchall():
            if row[0] != prevbuf:
                prevbuf = row[0]
                startoh = startohdict.get(prevbuf, 0)
                endoh = startoh + float(row[6] - row[7])
            else:
                startoh = endoh
                endoh += float(row[6] - row[7])
            yield {
                "buffer": row[0],
                "item": row[1],
                "location": row[2],
                "bucket": row[3],
                "startdate": row[4].date(),
                "enddate": row[5].date(),
                "startoh": round(startoh, 1),
                "produced": round(row[6], 1),
                "consumed": round(row[7], 1),
                "endoh": round(endoh, 1),
            }