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), }
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
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), }
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), }