class OverviewReport(GridPivot): ''' A report showing the inventory profile of buffers. ''' template = 'output/buffer.html' title = _('Inventory report') basequeryset = OperationPlanMaterial.objects.all().order_by('item_id', 'location_id').distinct('item_id', 'location_id') model = OperationPlanMaterial default_sort = (1, 'asc', 2, 'asc') permissions = (('view_inventory_report', 'Can view inventory report'),) help_url = 'user-guide/user-interface/plan-analysis/inventory-report.html' rows = ( GridFieldText('buffer', title=_('buffer'), editable=False, key=True, initially_hidden=True), GridFieldText('item', title=_('item'), editable=False, field_name='item__name', formatter='detail', extra='"role":"input/item"'), GridFieldText('location', title=_('location'), editable=False, field_name='location__name', formatter='detail', extra='"role":"input/location"'), # Optional fields referencing the item GridFieldText('item__description', title=string_concat(_('item'), ' - ', _('description')), initially_hidden=True, editable=False), GridFieldText('item__category', title=string_concat(_('item'), ' - ', _('category')), initially_hidden=True, editable=False), GridFieldText('item__subcategory', title=string_concat(_('item'), ' - ', _('subcategory')), initially_hidden=True, editable=False), GridFieldText('item__owner', title=string_concat(_('item'), ' - ', _('owner')), field_name='item__owner__name', initially_hidden=True, editable=False), GridFieldText('item__source', title=string_concat(_('item'), ' - ', _('source')), initially_hidden=True, editable=False), GridFieldLastModified('item__lastmodified', title=string_concat(_('item'), ' - ', _('last modified')), initially_hidden=True, editable=False), # Optional fields referencing the location GridFieldText('location__description', title=string_concat(_('location'), ' - ', _('description')), initially_hidden=True, editable=False), GridFieldText('location__category', title=string_concat(_('location'), ' - ', _('category')), initially_hidden=True, editable=False), GridFieldText('location__subcategory', title=string_concat(_('location'), ' - ', _('subcategory')), initially_hidden=True, editable=False), GridFieldText('location__available', title=string_concat(_('location'), ' - ', _('available')), initially_hidden=True, field_name='origin__available__name', formatter='detail', extra='"role":"input/calendar"', editable=False), GridFieldText('location__owner', title=string_concat(_('location'), ' - ', _('owner')), initially_hidden=True, field_name='origin__owner__name', formatter='detail', extra='"role":"input/location"', editable=False), GridFieldText('location__source', title=string_concat(_('location'), ' - ', _('source')), initially_hidden=True, editable=False), GridFieldLastModified('location__lastmodified', title=string_concat(_('location'), ' - ', _('last modified')), initially_hidden=True, editable=False), ) crosses = ( ('startoh', {'title': _('start inventory')}), ('produced', {'title': _('produced')}), ('consumed', {'title': _('consumed')}), ('endoh', {'title': _('end inventory')}), ) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 reportclass.attr_sql = '' # Adding custom item attributes for f in getAttributeFields(Item, initially_hidden=True): reportclass.rows += (f,) reportclass.attr_sql += 'item.%s, ' % f.name.split('__')[-1] # Adding custom location attributes for f in getAttributeFields(Location, related_name_prefix="location", initially_hidden=True): reportclass.rows += (f,) reportclass.attr_sql += 'location.%s, ' % f.name.split('__')[-1] @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session['lasttab'] = 'plan' return { 'title': force_text(Item._meta.verbose_name) + " " + args[0], 'post_title': _('plan') } else: return {} @classmethod 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) # Execute a query to get the onhand value at the start of our horizon startohdict = {} query = ''' select opplanmat.item_id, opplanmat.location_id, sum(oh.onhand) from (%s) opplanmat inner join ( select operationplanmaterial.item_id, operationplanmaterial.location_id, operationplanmaterial.onhand as onhand from operationplanmaterial, (select item_id, location_id, max(id) as id from operationplanmaterial where flowdate < '%s' group by item_id, location_id ) maxid where maxid.item_id = operationplanmaterial.item_id and maxid.location_id = operationplanmaterial.location_id and maxid.id = operationplanmaterial.id ) oh on oh.item_id = opplanmat.item_id and oh.location_id = opplanmat.location_id group by opplanmat.item_id, opplanmat.location_id ''' % (basesql, request.report_startdate) cursor.execute(query, baseparams) for row in cursor.fetchall(): startohdict[ "%s @ %s" % (row[0], row[1]) ] = float(row[2]) # Execute the actual query query = ''' select invplan.item_id || ' @ ' || invplan.location_id, invplan.item_id, invplan.location_id, item.description, item.category, item.subcategory, item.owner_id, item.source, item.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.owner_id, location.source, location.lastmodified, %s invplan.bucket, invplan.startdate, invplan.enddate, invplan.consumed, invplan.produced from ( select opplanmat.item_id, opplanmat.location_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) opplanmat -- 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 -- Consumed and produced quantities left join operationplanmaterial on opplanmat.item_id = operationplanmaterial.item_id and opplanmat.location_id = operationplanmaterial.location_id and d.startdate <= operationplanmaterial.flowdate and d.enddate > operationplanmaterial.flowdate and operationplanmaterial.flowdate >= %%s and operationplanmaterial.flowdate < %%s -- Grouping and sorting group by opplanmat.item_id, opplanmat.location_id, d.bucket, d.startdate, d.enddate ) invplan left outer join item on invplan.item_id = item.name left outer join location on invplan.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], 'item__description': row[3], 'item__category': row[4], 'item__subcategory': row[5], 'item__owner': row[6], 'item__source': row[7], 'item__lastmodified': row[8], 'location__description': row[9], 'location__category': row[10], 'location__subcategory': row[11], 'location__available_id': row[12], 'location__owner_id': row[13], 'location__source': row[14], 'location__lastmodified': row[15], '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 = 16 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
class DemandList(GridReport): template = "input/demand.html" title = _("sales orders") model = Demand frozenColumns = 1 help_url = "modeling-wizard/master-data/sales-orders.html" message_when_empty = Template(""" <h3>Define sales orders</h3> <br> The sales orders table contains all the orders placed by your customers.<br><br> Orders in the status "open" are still be delivered and will be planned.<br><br> <br><br> <div role="group" class="btn-group.btn-group-justified"> <a href="{{request.prefix}}/data/input/demand/add/" class="btn btn-primary">Create a single sales order<br>in a form</a> <a href="{{request.prefix}}/wizard/load/production/?currentstep=2" class="btn btn-primary">Wizard to upload sale orders<br>from a spreadsheet</a> </div> <br> """) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 reportclass.attr_sql = "" # Adding custom item attributes for f in getAttributeFields(Item, related_name_prefix="item", initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += "item.%s, " % f.name.split("__")[-1] # Adding custom location attributes for f in getAttributeFields(Location, related_name_prefix="location", initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += "location.%s, " % f.name.split( "__")[-1] # Adding custom customer attributes for f in getAttributeFields(Customer, related_name_prefix="customer", initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += "customer.%s, " % f.name.split( "__")[-1] # Adding custom demand attributes for f in getAttributeFields(Demand, initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += "demand.%s, " % f.name.split("__")[-1] @classmethod def basequeryset(reportclass, request, *args, **kwargs): q = Demand.objects.all() if "item" in request.GET: 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 = 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 = 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", [])) rows = ( GridFieldText( "name", title=_("name"), key=True, formatter="detail", extra='"role":"input/demand"', ), GridFieldHierarchicalText( "item", title=_("item"), field_name="item__name", formatter="detail", extra='"role":"input/item"', model=Item, ), GridFieldText("batch", title=_("batch"), initially_hidden=True), GridFieldHierarchicalText( "location", title=_("location"), field_name="location__name", formatter="detail", extra='"role":"input/location"', model=Location, ), GridFieldHierarchicalText( "customer", title=_("customer"), field_name="customer__name", formatter="detail", extra='"role":"input/customer"', model=Customer, ), GridFieldChoice("status", title=_("status"), choices=Demand.demandstatus), GridFieldNumber("quantity", title=_("quantity")), GridFieldDateTime("due", title=_("due")), GridFieldDuration("delay", title=_("delay"), editable=False, extra='"formatter":delayfmt'), GridFieldNumber( "plannedquantity", title=_("planned quantity"), editable=False, extra= '"formatoptions":{"defaultValue":""}, "cellattr":plannedquantitycellattr', ), GridFieldNumber( "plannedshort", title=_("quantity planned short"), editable=False, extra= '"formatoptions":{"defaultValue":""}, "cellattr":plannedquantitycellattr', ), GridFieldDateTime("deliverydate", title=_("delivery date"), editable=False), GridFieldText("description", title=_("description"), initially_hidden=True), GridFieldText("category", title=_("category"), initially_hidden=True), GridFieldText("subcategory", title=_("subcategory"), initially_hidden=True), GridFieldText( "operation", title=_("delivery operation"), field_name="operation__name", formatter="detail", extra='"role":"input/operation"', initially_hidden=True, ), GridFieldInteger("priority", title=_("priority")), GridFieldText( "owner", title=_("owner"), field_name="owner__name", formatter="detail", extra='"role":"input/demand"', initially_hidden=True, ), GridFieldDuration("maxlateness", title=_("maximum lateness"), initially_hidden=True), GridFieldNumber("minshipment", title=_("minimum shipment"), initially_hidden=True), GridFieldText("batch", title=_("batch"), field_name="batch", initially_hidden=True), GridFieldText("source", title=_("source"), initially_hidden=True), GridFieldLastModified("lastmodified"), # Optional fields referencing the item GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, formatter="detail", extra='"role":"input/item"', ), GridFieldCurrency( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the customer GridFieldText( "customer__description", title=format_lazy("{} - {}", _("customer"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "customer__category", title=format_lazy("{} - {}", _("customer"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "customer__subcategory", title=format_lazy("{} - {}", _("customer"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "customer__owner", title=format_lazy("{} - {}", _("customer"), _("owner")), initially_hidden=True, field_name="customer__owner__name", formatter="detail", extra='"role":"input/customer"', editable=False, ), GridFieldText( "customer__source", title=format_lazy("{} - {}", _("customer"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "customer__lastmodified", title=format_lazy("{} - {}", _("customer"), _("last modified")), initially_hidden=True, editable=False, ), ) if settings.ERP_CONNECTOR: actions = [{ "name": "erp_incr_export", "label": format_lazy("export to {erp}", erp=settings.ERP_CONNECTOR), "function": "ERPconnection.SODepExport(jQuery('#grid'),'SO')", }] else: actions = [ { "name": "inquiry", "label": format_lazy(_("change status to {status}"), status=_("inquiry")), "function": "grid.setStatus('inquiry')", }, { "name": "quote", "label": format_lazy(_("change status to {status}"), status=_("quote")), "function": "grid.setStatus('quote')", }, { "name": "open", "label": format_lazy(_("change status to {status}"), status=_("open")), "function": "grid.setStatus('open')", }, { "name": "closed", "label": format_lazy(_("change status to {status}"), status=_("closed")), "function": "grid.setStatus('closed')", }, { "name": "canceled", "label": format_lazy(_("change status to {status}"), status=_("canceled")), "function": "grid.setStatus('canceled')", }, ]
class OverviewReport(GridPivot): ''' A report showing the inventory profile of buffers. ''' template = 'output/buffer.html' title = _('Inventory report') @classmethod def basequeryset(reportclass, request, *args, **kwargs): if len(args) and args[0]: return Buffer.objects.all() else: return OperationPlanMaterial.objects.all() \ .order_by('item_id', 'location_id') \ .distinct('item_id', 'location_id') \ .annotate(buffer=Concat('item', Value(' @ '), 'location')) model = OperationPlanMaterial default_sort = (1, 'asc', 2, 'asc') permissions = (('view_inventory_report', 'Can view inventory report'),) help_url = 'user-guide/user-interface/plan-analysis/inventory-report.html' rows = ( GridFieldText('buffer', title=_('buffer'), editable=False, key=True, initially_hidden=True), GridFieldText('item', title=_('item'), editable=False, field_name='item__name', formatter='detail', extra='"role":"input/item"'), GridFieldText('location', title=_('location'), editable=False, field_name='location__name', formatter='detail', extra='"role":"input/location"'), # Optional fields referencing the item GridFieldText('item__description', title=string_concat(_('item'), ' - ', _('description')), initially_hidden=True, editable=False), GridFieldText('item__category', title=string_concat(_('item'), ' - ', _('category')), initially_hidden=True, editable=False), GridFieldText('item__subcategory', title=string_concat(_('item'), ' - ', _('subcategory')), initially_hidden=True, editable=False), GridFieldText('item__owner', title=string_concat(_('item'), ' - ', _('owner')), field_name='item__owner__name', initially_hidden=True, editable=False), GridFieldText('item__source', title=string_concat(_('item'), ' - ', _('source')), initially_hidden=True, editable=False), GridFieldLastModified('item__lastmodified', title=string_concat(_('item'), ' - ', _('last modified')), initially_hidden=True, editable=False), # Optional fields referencing the location GridFieldText('location__description', title=string_concat(_('location'), ' - ', _('description')), initially_hidden=True, editable=False), GridFieldText('location__category', title=string_concat(_('location'), ' - ', _('category')), initially_hidden=True, editable=False), GridFieldText('location__subcategory', title=string_concat(_('location'), ' - ', _('subcategory')), initially_hidden=True, editable=False), GridFieldText('location__available', title=string_concat(_('location'), ' - ', _('available')), initially_hidden=True, field_name='origin__available__name', formatter='detail', extra='"role":"input/calendar"', editable=False), GridFieldText('location__owner', title=string_concat(_('location'), ' - ', _('owner')), initially_hidden=True, field_name='origin__owner__name', formatter='detail', extra='"role":"input/location"', editable=False), GridFieldText('location__source', title=string_concat(_('location'), ' - ', _('source')), initially_hidden=True, editable=False), GridFieldLastModified('location__lastmodified', title=string_concat(_('location'), ' - ', _('last modified')), initially_hidden=True, editable=False), ) crosses = ( ('startoh', {'title': _('start inventory')}), ('startohdoc', {'title': _('start inventory days of cover')}), ('safetystock', {'title': _('safety stock')}), ('consumed', {'title': _('total consumed')}), ('consumedMO', {'title': _('consumed by MO')}), ('consumedDO', {'title': _('consumed by DO')}), ('consumedSO', {'title': _('consumed by SO')}), ('produced', {'title': _('total produced')}), ('producedMO', {'title': _('produced by MO')}), ('producedDO', {'title': _('produced by DO')}), ('producedPO', {'title': _('produced by PO')}), ('endoh', {'title': _('end inventory')}), ) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 reportclass.attr_sql = '' # Adding custom item attributes for f in getAttributeFields(Item, initially_hidden=True): reportclass.rows += (f,) reportclass.attr_sql += 'item.%s, ' % f.name.split('__')[-1] # Adding custom location attributes for f in getAttributeFields(Location, related_name_prefix="location", initially_hidden=True): reportclass.rows += (f,) reportclass.attr_sql += 'location.%s, ' % f.name.split('__')[-1] @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session['lasttab'] = 'plan' return { 'title': force_text(Buffer._meta.verbose_name) + " " + args[0], 'post_title': _('plan') } else: return {} @classmethod 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) # Execute the actual query query = ''' select item.name||' @ '||location.name, item.name item_id, location.name location_id, item.description, item.category, item.subcategory, item.owner_id, item.source, item.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.owner_id, location.source, location.lastmodified, %s coalesce((select onhand from operationplanmaterial where item_id = item.name and location_id = location.name and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1),0) startoh, coalesce((select onhand from operationplanmaterial where item_id = item.name and location_id = location.name and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1),0) - coalesce(-sum(least(operationplanmaterial.quantity, 0)),0) + coalesce(sum(greatest(operationplanmaterial.quantity, 0)),0) endoh, case when coalesce((select onhand from operationplanmaterial where item_id = item.name and location_id = location.name and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1),0) = 0 then 0 when (select to_char(flowdate,'YYYY-MM-DD')||' '||round(periodofcover/86400) from operationplanmaterial where item_id = item.name and location_id = location.name and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1) = '1971-01-01 999' then 999 else extract( epoch from (select flowdate from operationplanmaterial where item_id = item.name and location_id = location.name and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1) + coalesce((select periodofcover from operationplanmaterial where item_id = item.name and location_id = location.name and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1),0) * interval '1 second' - greatest(d.startdate,%%s))/86400 end startohdoc, d.bucket, d.startdate, d.enddate, coalesce((select minimum from operationplanmaterial where item_id = item.name and location_id = location.name and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1),0) safetystock, coalesce(-sum(least(operationplanmaterial.quantity, 0)),0) as consumed, coalesce(-sum(least(case when operationplan.type = 'MO' then operationplanmaterial.quantity else 0 end, 0)),0) as consumedMO, coalesce(-sum(least(case when operationplan.type = 'DO' then operationplanmaterial.quantity else 0 end, 0)),0) as consumedDO, coalesce(-sum(least(case when operationplan.type = 'DLVR' then operationplanmaterial.quantity else 0 end, 0)),0) as consumedSO, coalesce(sum(greatest(operationplanmaterial.quantity, 0)),0) as produced, coalesce(sum(greatest(case when operationplan.type = 'MO' then operationplanmaterial.quantity else 0 end, 0)),0) as producedMO, coalesce(sum(greatest(case when operationplan.type = 'DO' then operationplanmaterial.quantity else 0 end, 0)),0) as producedDO, coalesce(sum(greatest(case when operationplan.type = 'PO' then operationplanmaterial.quantity else 0 end, 0)),0) as producedPO from (%s) opplanmat inner join item on item.name = opplanmat.item_id inner join location on location.name = opplanmat.location_id -- 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 -- Consumed and produced quantities left join operationplanmaterial on opplanmat.item_id = operationplanmaterial.item_id and opplanmat.location_id = operationplanmaterial.location_id and d.startdate <= operationplanmaterial.flowdate and d.enddate > operationplanmaterial.flowdate and operationplanmaterial.flowdate >= greatest(d.startdate,%%s) and operationplanmaterial.flowdate < d.enddate left outer join operationplan on operationplan.id = operationplanmaterial.operationplan_id group by item.name, location.name, item.description, item.category, item.subcategory, item.owner_id, item.source, item.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.owner_id, location.source, location.lastmodified, d.bucket, d.startdate, d.enddate order by %s, d.startdate ''' % ( reportclass.attr_sql, basesql, sortsql ) cursor.execute( query, ( request.report_startdate, # startoh request.report_startdate, # endoh request.report_startdate, # startohdoc request.report_startdate, # startohdoc request.report_startdate, # startohdoc request.report_startdate, # startohdoc request.report_startdate, # startohdoc request.report_startdate,) # safetystock + baseparams + # opplanmat (request.report_bucket, request.report_startdate, request.report_enddate, # bucket d request.report_startdate, # operationplanmaterial ) ) # Build the python result for row in cursor.fetchall(): numfields = len(row) res = { 'buffer': row[0], 'item': row[1], 'location': row[2], 'item__description': row[3], 'item__category': row[4], 'item__subcategory': row[5], 'item__owner': row[6], 'item__source': row[7], 'item__lastmodified': row[8], 'location__description': row[9], 'location__category': row[10], 'location__subcategory': row[11], 'location__available_id': row[12], 'location__owner_id': row[13], 'location__source': row[14], 'location__lastmodified': row[15], 'startoh': round(row[numfields - 15], 1), 'endoh': round(row[numfields - 14], 1), 'startohdoc': int(row[numfields - 13]), 'bucket': row[numfields - 12], 'startdate': row[numfields - 11].date(), 'enddate': row[numfields - 10].date(), 'safetystock': round(row[numfields - 9] or 0, 1), 'consumed': round(row[numfields - 8], 1), 'consumedMO': round(row[numfields - 7], 1), 'consumedDO': round(row[numfields - 6], 1), 'consumedSO': round(row[numfields - 5], 1), 'produced': round(row[numfields - 4], 1), 'producedMO': round(row[numfields - 3], 1), 'producedDO': round(row[numfields - 2], 1), 'producedPO': round(row[numfields - 1], 1), } # Add attribute fields idx = 16 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
class ItemDistributionList(GridReport): title = _("item distributions") basequeryset = ItemDistribution.objects.all() model = ItemDistribution frozenColumns = 1 help_url = "modeling-wizard/distribution/item-distributions.html" rows = ( GridFieldInteger( "id", title=_("identifier"), key=True, formatter="detail", extra='"role":"input/itemdistribution"', initially_hidden=True, ), GridFieldHierarchicalText( "item", title=_("item"), field_name="item__name", formatter="detail", extra='"role":"input/item"', model=Item, ), GridFieldHierarchicalText( "location", title=_("location"), field_name="location__name", formatter="detail", extra='"role":"input/location"', model=Location, ), GridFieldHierarchicalText( "origin", title=_("origin"), field_name="origin__name", formatter="detail", extra='"role":"input/location"', model=Location, ), GridFieldDuration("leadtime", title=_("lead time")), GridFieldNumber("sizeminimum", title=_("size minimum")), GridFieldNumber("sizemultiple", title=_("size multiple")), GridFieldNumber("sizemaximum", title=_("size maximum"), initially_hidden=True), GridFieldCurrency("cost", title=_("cost"), initially_hidden=True), GridFieldInteger("priority", title=_("priority"), initially_hidden=True), GridFieldDuration("fence", title=_("fence"), initially_hidden=True), GridFieldDateTime( "effective_start", title=_("effective start"), initially_hidden=True ), GridFieldDateTime( "effective_end", title=_("effective end"), initially_hidden=True ), GridFieldText( "resource", title=_("resource"), field_name="resource__name", formatter="detail", extra='"role":"input/resource"', initially_hidden=True, ), GridFieldNumber( "resource_qty", title=_("resource quantity"), initially_hidden=True ), GridFieldText("source", title=_("source"), initially_hidden=True), GridFieldLastModified("lastmodified"), # Optional fields referencing the item GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldCurrency( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the origin location GridFieldText( "origin__description", title=format_lazy("{} - {}", _("origin"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "origin__category", title=format_lazy("{} - {}", _("origin"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "origin__subcategory", title=format_lazy("{} - {}", _("origin"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "origin__available", title=format_lazy("{} - {}", _("origin"), _("available")), initially_hidden=True, field_name="origin__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "origin__owner", title=format_lazy("{} - {}", _("origin"), _("owner")), initially_hidden=True, field_name="origin__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "origin__source", title=format_lazy("{} - {}", _("origin"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "origin__lastmodified", title=format_lazy("{} - {}", _("origin"), _("last modified")), initially_hidden=True, editable=False, ), )
class InventoryDetail(OperationPlanMixin, GridReport): """ A list report to show OperationPlanMaterial. """ template = "input/operationplanreport.html" title = _("Inventory detail") model = OperationPlanMaterial permissions = (("view_inventory_report", "Can view inventory report"),) frozenColumns = 0 editable = True multiselect = True height = 250 help_url = "user-interface/plan-analysis/inventory-detail-report.html" @classmethod def basequeryset(reportclass, request, *args, **kwargs): if len(args) and args[0]: if request.path_info.startswith( "/data/input/operationplanmaterial/item/" ) or request.path_info.startswith("/detail/input/item/"): base = OperationPlanMaterial.objects.filter(item=args[0]) elif request.path_info.startswith( "/data/input/operationplanmaterial/buffer/" ): i_b_l = args[0].split(" @ ") if len(i_b_l) == 1: buffer = Buffer.objects.get(id=args[0]) base = OperationPlanMaterial.objects.filter( item=buffer.item.name, location=buffer.location.name ) elif len(i_b_l) == 2: base = OperationPlanMaterial.objects.filter( item=i_b_l[0], location=i_b_l[1] ) else: base = OperationPlanMaterial.objects.filter( item=i_b_l[0], location=i_b_l[2], operationplan__batch=i_b_l[1] ) else: base = OperationPlanMaterial.objects base = reportclass.operationplanExtraBasequery(base, request) return base.select_related().annotate( feasible=RawSQL( "coalesce((operationplan.plan->>'feasible')::boolean, true)", [] ) ) @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: if request.path_info.startswith( "/data/input/operationplanmaterial/item/" ) or request.path_info.startswith("/detail/input/item/"): request.session["lasttab"] = "inventorydetail" return { "active_tab": "inventorydetail", "model": Item, "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": _("inventory detail"), } elif request.path_info.startswith( "/data/input/operationplanmaterial/buffer/" ): request.session["lasttab"] = "plandetail" dlmtr = args[0].find(" @ ") if dlmtr != -1: item = args[0][:dlmtr] location = args[0][dlmtr + 3 :] else: buffer = Buffer.objects.get(id=args[0]) item = buffer.item.name location = buffer.location.name return { "active_tab": "plandetail", "model": Buffer, "title": force_text(Buffer._meta.verbose_name) + " " + item + " @ " + location, "post_title": _("plan detail"), } else: return {"active_tab": "plandetail", "model": OperationPlanMaterial} rows = ( GridFieldInteger( "id", title=_("identifier"), key=True, editable=False, formatter="detail", extra='"role":"input/operationplanmaterial"', initially_hidden=True, ), GridFieldText( "item", title=_("item"), field_name="item__name", editable=False, formatter="detail", extra='"role":"input/item"', ), GridFieldText( "location", title=_("location"), field_name="location__name", editable=False, formatter="detail", extra='"role":"input/location"', ), GridFieldText("operationplan__reference", title=_("reference"), editable=False), GridFieldText( "owner", title=_("owner"), field_name="operationplan__owner__reference", formatter="detail", extra="role:'input/manufacturingorder'", initially_hidden=True, ), GridFieldText( "operationplan__batch", title=_("batch"), editable=False, initially_hidden=True, ), GridFieldText( "color", title=_("inventory status"), formatter="color", field_name="operationplan__color", width="125", editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldText( "operationplan__type", title=_("type"), field_name="operationplan__type", editable=False, ), GridFieldText( "operationplan__name", title=_("operation"), editable=False, field_name="operationplan__name", formatter="detail", extra='"role":"input/operation"', ), GridFieldText( "operationplan__operation__description", title=format_lazy("{} - {}", _("operation"), _("description")), editable=False, initially_hidden=True, ), GridFieldText( "operationplan__operation__category", title=format_lazy("{} - {}", _("operation"), _("category")), editable=False, initially_hidden=True, ), GridFieldText( "operationplan__operation__subcategory", title=format_lazy("{} - {}", _("operation"), _("subcategory")), editable=False, initially_hidden=True, ), GridFieldText( "operationplan__operation__type", title=format_lazy("{} - {}", _("operation"), _("type")), initially_hidden=True, ), GridFieldDuration( "operationplan__operation__duration", title=format_lazy("{} - {}", _("operation"), _("duration")), initially_hidden=True, ), GridFieldDuration( "operationplan__operation__duration_per", title=format_lazy("{} - {}", _("operation"), _("duration per unit")), initially_hidden=True, ), GridFieldDuration( "operationplan__operation__fence", title=format_lazy("{} - {}", _("operation"), _("release fence")), initially_hidden=True, ), GridFieldDuration( "operationplan__operation__posttime", title=format_lazy("{} - {}", _("operation"), _("post-op time")), initially_hidden=True, ), GridFieldNumber( "operationplan__operation__sizeminimum", title=format_lazy("{} - {}", _("operation"), _("size minimum")), initially_hidden=True, ), GridFieldNumber( "operationplan__operation__sizemultiple", title=format_lazy("{} - {}", _("operation"), _("size multiple")), initially_hidden=True, ), GridFieldNumber( "operationplan__operation__sizemaximum", title=format_lazy("{} - {}", _("operation"), _("size maximum")), initially_hidden=True, ), GridFieldInteger( "operationplan__operation__priority", title=format_lazy("{} - {}", _("operation"), _("priority")), initially_hidden=True, ), GridFieldDateTime( "operationplan__operation__effective_start", title=format_lazy("{} - {}", _("operation"), _("effective start")), initially_hidden=True, ), GridFieldDateTime( "operationplan__operation__effective_end", title=format_lazy("{} - {}", _("operation"), _("effective end")), initially_hidden=True, ), GridFieldCurrency( "operationplan__operation__cost", title=format_lazy("{} - {}", _("operation"), _("cost")), initially_hidden=True, ), GridFieldText( "operationplan__operation__search", title=format_lazy("{} - {}", _("operation"), _("search mode")), initially_hidden=True, ), GridFieldText( "operationplan__operation__source", title=format_lazy("{} - {}", _("operation"), _("source")), initially_hidden=True, ), GridFieldLastModified( "operationplan__operation__lastmodified", title=format_lazy("{} - {}", _("operation"), _("last modified")), initially_hidden=True, ), GridFieldDateTime( "flowdate", title=_("date"), editable=False, extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"min"', ), GridFieldNumber( "quantity", title=_("quantity"), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldNumber( "onhand", title=_("expected onhand"), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldDuration( "periodofcover", title=_("period of cover"), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldDateTime( "operationplan__startdate", title=_("start date"), editable=False, extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"min"', ), GridFieldDateTime( "operationplan__enddate", title=_("end date"), editable=False, extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"max"', ), GridFieldText( "operationplan__status", title=_("status"), editable=False, field_name="operationplan__status", ), GridFieldNumber( "operationplan__criticality", title=_("criticality"), field_name="operationplan__criticality", editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldDuration( "delay", title=_("delay"), field_name="operationplan__delay", editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"', ), GridFieldNumber( "operationplan__quantity", title=_("operationplan quantity"), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldText( "demands", title=_("demands"), formatter="demanddetail", extra='"role":"input/demand"', width=300, editable=False, sortable=False, ), GridFieldBool( "feasible", title=_("feasible"), editable=False, initially_hidden=True, search=False, ), # Optional fields referencing the item GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldCurrency( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), GridFieldChoice( "status", title=_("material status"), choices=OperationPlanMaterial.OPMstatus, ), GridFieldLastModified("lastmodified", initially_hidden=True), )
class ResourceList(GridReport): title = _("resources") basequeryset = Resource.objects.all() model = Resource frozenColumns = 1 help_url = "modeling-wizard/manufacturing-capacity/resources.html" message_when_empty = Template(""" <h3>Define resources</h3> <br> Resources represent capacity.<br> They represent a machine, a group of machines, an operator, a group of operators, or some logical capacity constraint.<br> <br><br> <div role="group" class="btn-group.btn-group-justified"> <a href="{{request.prefix}}/data/input/resource/add/" class="btn btn-primary">Create a single resource<br>in a form</a> <a href="{{request.prefix}}/wizard/load/production/?currentstep=8" class="btn btn-primary">Wizard to upload resources<br>from a spreadsheet</a> </div> <br> """) rows = ( GridFieldText( "name", title=_("name"), key=True, formatter="detail", extra='"role":"input/resource"', ), GridFieldText("description", title=_("description")), GridFieldText("category", title=_("category"), initially_hidden=True), GridFieldText("subcategory", title=_("subcategory"), initially_hidden=True), GridFieldHierarchicalText( "location", title=_("location"), field_name="location__name", formatter="detail", extra='"role":"input/location"', model=Location, ), GridFieldText( "owner", title=_("owner"), field_name="owner__name", formatter="detail", extra='"role":"input/resource"', initially_hidden=True, ), GridFieldChoice("type", title=_("type"), choices=Resource.types), GridFieldBool("constrained", title=_("constrained")), GridFieldNumber("maximum", title=_("maximum")), GridFieldText( "maximum_calendar", title=_("maximum calendar"), field_name="maximum_calendar__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldText( "available", title=_("available"), field_name="available__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldCurrency("cost", title=_("cost"), initially_hidden=True), GridFieldDuration("maxearly", title=_("maxearly"), initially_hidden=True), GridFieldText( "setupmatrix", title=_("setup matrix"), field_name="setupmatrix__name", formatter="detail", extra='"role":"input/setupmatrix"', initially_hidden=True, ), GridFieldText("setup", title=_("setup"), initially_hidden=True), GridFieldText("source", title=_("source"), initially_hidden=True), # Translator: xgettext:no-python-format GridFieldNumber("efficiency", title=_("efficiency %"), formatter="percentage"), GridFieldText( "efficiency_calendar", # Translator: xgettext:no-python-format title=_("efficiency % calendar"), initially_hidden=True, field_name="efficiency_calendar__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldLastModified("lastmodified"), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), )
class OverviewReport(GridPivot): """ A report showing the inventory profile of buffers. """ template = "output/buffer.html" title = _("Inventory report") new_arg_logic = True @classmethod def basequeryset(reportclass, request, *args, **kwargs): if hasattr(request, "basequeryset"): return request.basequeryset item = None location = None batch = None if len(args) and args[0]: if request.path_info.startswith( "/buffer/item/" ) or request.path_info.startswith("/detail/input/item/"): item = args[0] else: i_b_l = args[0].split(" @ ") if len(i_b_l) == 1: b = Buffer.objects.values("item", "location").get(id=args[0]) item = b["item"] location = b["location"] elif len(i_b_l) == 2: item = i_b_l[0] location = i_b_l[1] else: item = i_b_l[0] location = i_b_l[2] batch = i_b_l[1] request.basequeryset = OperationPlanMaterial.objects.values( "item", "location", "item__type" ).filter( ((Q(item__type="make to stock") | Q(item__type__isnull=True))) | (Q(item__type="make to order") & Q(operationplan__batch__isnull=False)) ) if item: request.basequeryset = request.basequeryset.filter(item=item) if location: request.basequeryset = request.basequeryset.filter(location=location) if batch: request.basequeryset = request.basequeryset.filter( operationplan__batch=batch ) request.basequeryset = request.basequeryset.annotate( buffer=RawSQL( "operationplanmaterial.item_id || " "(case when item.type is distinct from 'make to order' then '' else ' @ ' || operationplan.batch end) " "|| ' @ ' || operationplanmaterial.location_id", (), ), opplan_batch=RawSQL( "case when item.type is distinct from 'make to order' then '' else operationplan.batch end", (), ), ).distinct() return request.basequeryset model = OperationPlanMaterial default_sort = (1, "asc", 2, "asc") permissions = (("view_inventory_report", "Can view inventory report"),) help_url = "user-interface/plan-analysis/inventory-report.html" rows = ( GridFieldText( "buffer", title=_("buffer"), editable=False, key=True, initially_hidden=True ), GridFieldText( "item", title=_("item"), editable=False, field_name="item__name", formatter="detail", extra='"role":"input/item"', ), GridFieldText( "location", title=_("location"), editable=False, field_name="location__name", formatter="detail", extra='"role":"input/location"', ), # Optional fields referencing the item GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), GridFieldText( "batch", title=_("batch"), field_name="opplan_batch", editable=False, initially_hidden=True, ), ) crosses = ( ("startoh", {"title": _("start inventory")}), ( "startohdoc", {"title": _("start inventory days of cover"), "initially_hidden": True}, ), ("safetystock", {"title": _("safety stock")}), ("consumed", {"title": _("total consumed")}), ("consumedMO", {"title": _("consumed by MO"), "initially_hidden": True}), ("consumedDO", {"title": _("consumed by DO"), "initially_hidden": True}), ("consumedSO", {"title": _("consumed by SO"), "initially_hidden": True}), ("produced", {"title": _("total produced")}), ("producedMO", {"title": _("produced by MO"), "initially_hidden": True}), ("producedDO", {"title": _("produced by DO"), "initially_hidden": True}), ("producedPO", {"title": _("produced by PO"), "initially_hidden": True}), ("endoh", {"title": _("end inventory")}), ( "total_in_progress", {"title": _("total in progress"), "initially_hidden": True}, ), ( "work_in_progress_mo", {"title": _("work in progress MO"), "initially_hidden": True}, ), ("on_order_po", {"title": _("on order PO"), "initially_hidden": True}), ("in_transit_do", {"title": _("in transit DO"), "initially_hidden": True}), ) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 reportclass.attr_sql = "" # Adding custom item attributes for f in getAttributeFields( Item, related_name_prefix="item", initially_hidden=True ): reportclass.rows += (f,) reportclass.attr_sql += "item.%s, " % f.name.split("__")[-1] # Adding custom location attributes for f in getAttributeFields( Location, related_name_prefix="location", initially_hidden=True ): reportclass.rows += (f,) reportclass.attr_sql += "location.%s, " % f.name.split("__")[-1] @classmethod def extra_context(reportclass, request, *args, **kwargs): if not hasattr(request, "basequeryset"): reportclass.basequeryset(request, *args, **kwargs) if args and args[0]: if request.path_info.startswith( "/buffer/item/" ) or request.path_info.startswith("/detail/input/item/"): request.session["lasttab"] = "inventory" r = { "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": _("inventory"), "active_tab": "inventory", "model": Item, } if request.basequeryset.using(request.database).count() <= 1: r["args"] = args r["mode"] = "table" else: r["args"] = None return r else: request.session["lasttab"] = "plan" index = args[0].find(" @ ") if index == -1: buffer = Buffer.objects.get(id=args[0]) return { "title": force_text(Buffer._meta.verbose_name) + " " + ( args[0] if index != -1 else buffer.item.name + " @ " + buffer.location.name ), "post_title": _("plan"), "active_tab": "plan", "mode": "table", "model": Buffer, } else: return {} @classmethod def query(reportclass, request, basequery, sortsql="1 asc"): basesql, baseparams = basequery.query.get_compiler(basequery.db).as_sql( with_col_aliases=False ) # Execute the actual query query = """ select opplanmat.buffer, item.name item_id, location.name location_id, item.description, item.type, item.category, item.subcategory, item.cost, item.owner_id, item.source, item.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.owner_id, location.source, location.lastmodified, opplanmat.opplan_batch, %s case when d.history then jsonb_build_object( 'onhand', min(ax_buffer.onhand) ) else ( select jsonb_build_object( 'onhand', onhand, 'flowdate', to_char(flowdate,'YYYY-MM-DD HH24:MI:SS'), 'periodofcover', periodofcover ) from operationplanmaterial inner join operationplan on operationplanmaterial.operationplan_id = operationplan.reference where operationplanmaterial.item_id = item.name and operationplanmaterial.location_id = location.name and (item.type is distinct from 'make to order' or operationplan.batch is not distinct from opplanmat.opplan_batch) and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1 ) end as startoh, d.bucket, d.startdate, d.enddate, d.history, case when d.history then min(ax_buffer.safetystock) else (select safetystock from ( select 1 as priority, coalesce( (select value from calendarbucket where calendar_id = 'SS for ' || opplanmat.buffer and greatest(d.startdate,%%s) >= startdate and greatest(d.startdate,%%s) < enddate order by priority limit 1), (select defaultvalue from calendar where name = 'SS for ' || opplanmat.buffer) ) as safetystock union all select 2 as priority, coalesce( (select value from calendarbucket where calendar_id = ( select minimum_calendar_id from buffer where item_id = item.name and location_id = location.name and (item.type is distinct from 'make to order' or buffer.batch is not distinct from opplanmat.opplan_batch) ) and greatest(d.startdate,%%s) >= startdate and greatest(d.startdate,%%s) < enddate order by priority limit 1), (select defaultvalue from calendar where name = ( select minimum_calendar_id from buffer where item_id = item.name and location_id = location.name and (item.type is distinct from 'make to order' or buffer.batch is not distinct from opplanmat.opplan_batch) ) ) ) as safetystock union all select 3 as priority, minimum as safetystock from buffer where item_id = item.name and location_id = location.name and (item.type is distinct from 'make to order' or buffer.batch is not distinct from opplanmat.opplan_batch) ) t where t.safetystock is not null order by priority limit 1) end as safetystock, case when d.history then jsonb_build_object() else ( select jsonb_build_object( 'work_in_progress_mo', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 and operationplan.type = 'MO' then opm.quantity else 0 end), 'on_order_po', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 and operationplan.type = 'PO' then opm.quantity else 0 end), 'in_transit_do', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 and operationplan.type = 'DO' then opm.quantity else 0 end), 'total_in_progress', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'consumed', sum(case when (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'consumedMO', sum(case when operationplan.type = 'MO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'consumedDO', sum(case when operationplan.type = 'DO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'consumedSO', sum(case when operationplan.type = 'DLVR' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'produced', sum(case when (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'producedMO', sum(case when operationplan.type = 'MO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'producedDO', sum(case when operationplan.type = 'DO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'producedPO', sum(case when operationplan.type = 'PO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end) ) from operationplanmaterial opm inner join operationplan on operationplan.reference = opm.operationplan_id and ((startdate < d.enddate and enddate >= d.enddate) or (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate)) where opm.item_id = item.name and opm.location_id = location.name and (item.type is distinct from 'make to order' or operationplan.batch is not distinct from opplanmat.opplan_batch) ) end as ongoing from (%s) opplanmat inner join item on item.name = opplanmat.item_id inner join location on location.name = opplanmat.location_id -- Multiply with buckets cross join ( select name as bucket, startdate, enddate, min(snapshot_date) as snapshot_date, enddate < %%s as history from common_bucketdetail left outer join ax_manager on snapshot_date >= common_bucketdetail.startdate and snapshot_date < common_bucketdetail.enddate where common_bucketdetail.bucket_id = %%s and common_bucketdetail.enddate > %%s and common_bucketdetail.startdate < %%s group by common_bucketdetail.name, common_bucketdetail.startdate, common_bucketdetail.enddate ) d -- join with the archive data left outer join ax_buffer on ax_buffer.snapshot_date_id = d.snapshot_date and ax_buffer.item = opplanmat.item_id and ax_buffer.location = opplanmat.location_id and (ax_buffer.batch = opplanmat.opplan_batch or ax_buffer.batch is null) group by opplanmat.buffer, item.name, location.name, item.description, item.type, item.category, item.subcategory, item.cost, item.owner_id, item.source, item.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.owner_id, location.source, location.lastmodified, opplanmat.opplan_batch, d.bucket, d.startdate, d.enddate, d.history order by %s, d.startdate """ % ( reportclass.attr_sql, basesql, 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, # startoh request.report_startdate, request.report_startdate, request.report_startdate, request.report_startdate, # safetystock ) + (request.report_startdate,) * 9 + baseparams # ongoing + ( # opplanmat request.current_date, request.report_bucket, request.report_startdate, request.report_enddate, ), # bucket d ) for row in cursor_chunked: numfields = len(row) history = row[numfields - 3] res = { "buffer": row[0], "item": row[1], "location": row[2], "item__description": row[3], "item__type": row[4], "item__category": row[5], "item__subcategory": row[6], "item__cost": row[7], "item__owner": row[8], "item__source": row[9], "item__lastmodified": row[10], "location__description": row[11], "location__category": row[12], "location__subcategory": row[13], "location__available": row[14], "location__owner": row[15], "location__source": row[16], "location__lastmodified": row[17], "batch": row[18], "startoh": row[numfields - 7]["onhand"] if row[numfields - 7] else 0, "startohdoc": None if history else max( 0, 0 if ( row[numfields - 7]["onhand"] if row[numfields - 7] else 0 ) <= 0 else ( 999 if row[numfields - 7]["periodofcover"] == 86313600 else ( datetime.strptime( row[numfields - 7]["flowdate"], "%Y-%m-%d %H:%M:%S", ) + timedelta( seconds=row[numfields - 7]["periodofcover"] ) - row[numfields - 4] ).days if row[numfields - 7]["periodofcover"] else 999 ), ), "bucket": row[numfields - 6], "startdate": row[numfields - 5], "enddate": row[numfields - 4], "history": history, "safetystock": row[numfields - 2] if history else row[numfields - 2] or 0, "consumed": None if history else row[numfields - 1]["consumed"] or 0, "consumedMO": None if history else row[numfields - 1]["consumedMO"] or 0, "consumedDO": None if history else row[numfields - 1]["consumedDO"] or 0, "consumedSO": None if history else row[numfields - 1]["consumedSO"] or 0, "produced": None if history else row[numfields - 1]["produced"] or 0, "producedMO": None if history else row[numfields - 1]["producedMO"] or 0, "producedDO": None if history else row[numfields - 1]["producedDO"] or 0, "producedPO": None if history else row[numfields - 1]["producedPO"] or 0, "total_in_progress": None if history else row[numfields - 1]["total_in_progress"] or 0, "work_in_progress_mo": None if history else row[numfields - 1]["work_in_progress_mo"] or 0, "on_order_po": None if history else row[numfields - 1]["on_order_po"] or 0, "in_transit_do": None if history else row[numfields - 1]["in_transit_do"] or 0, "endoh": None if history else ( float( row[numfields - 7]["onhand"] if row[numfields - 7] else 0 ) + float(row[numfields - 1]["produced"] or 0) - float(row[numfields - 1]["consumed"] or 0) ), } # Add attribute fields idx = 18 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
class DistributionReport(GridPivot): ''' A report summarizing all distribution orders. ''' template = 'output/distribution_order_summary.html' title = _('Distribution order summary') model = DistributionOrder help_url = 'user-guide/user-interface/plan-analysis/distribution-order-summary.html' rows = ( GridFieldText('key', key=True, search=False, initially_hidden=True, hidden=True, field_name="item__name", editable=False), GridFieldText('item', title=_('item'), editable=False, field_name='item__name', formatter='detail', extra='"role":"input/item"'), GridFieldText('item__description', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('description'))), GridFieldText('item__category', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('category'))), GridFieldText('item__subcategory', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('subcategory'))), GridFieldCurrency('item__cost', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('cost')), field_name='item__cost'), GridFieldText('item__owner', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('owner')), field_name='item__owner__name'), GridFieldText('item__source', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('source'))), GridFieldLastModified('item__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('last modified'))), GridFieldText('origin', title=_('origin'), editable=False, field_name='origin__name', formatter='detail', extra='"role":"input/location"'), GridFieldText('origin__description', editable=False, initially_hidden=True, title=string_concat(_('origin'), ' - ', _('description'))), GridFieldText('origin__category', editable=False, initially_hidden=True, title=string_concat(_('origin'), ' - ', _('category'))), GridFieldText('origin__subcategory', editable=False, initially_hidden=True, title=string_concat(_('origin'), ' - ', _('subcategory'))), GridFieldText('origin__available', editable=False, initially_hidden=True, title=string_concat(_('origin'), ' - ', _('available')), field_name='origin__available__name', formatter='detail', extra='"role":"input/calendar"'), GridFieldLastModified('origin__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('origin'), ' - ', _('last modified'))), GridFieldText('destination', title=_('destination'), editable=False, field_name='destination__name', formatter='detail', extra='"role":"input/location"'), GridFieldText('destination__description', editable=False, initially_hidden=True, title=string_concat(_('destination'), ' - ', _('description'))), GridFieldText('destination__category', editable=False, initially_hidden=True, title=string_concat(_('destination'), ' - ', _('category'))), GridFieldText('destination__subcategory', editable=False, initially_hidden=True, title=string_concat(_('destination'), ' - ', _('subcategory'))), GridFieldText('destination__available', editable=False, initially_hidden=True, title=string_concat(_('destination'), ' - ', _('available')), field_name='destination__available__name', formatter='detail', extra='"role":"input/calendar"'), GridFieldLastModified('destination__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('destination'), ' - ', _('last modified'))), ) crosses = ( ('proposed_start', { 'title': _('proposed shipping') }), ('total_start', { 'title': _('total shipping') }), ('proposed_end', { 'title': _('proposed receiving') }), ('total_end', { 'title': _('total receiving') }), ('proposed_in_transit', { 'title': _('proposed in transit') }), ('total_in_transit', { 'title': _('total in transit') }), ) @staticmethod def basequeryset(request, *args, **kwargs): current, start, end = getHorizon(request) return DistributionOrder.objects.all() \ .filter(startdate__lte=end, enddate__gte=start) \ .annotate(key=RawSQL("coalesce(item_id,'') || coalesce(origin_id,'') || coalesce(destination_id,'')", ())) \ .distinct('key') \ .order_by() # Ordering isn't compatible with the distinct @classmethod def _apply_sort(reportclass, request, query): ''' Applies a sort to the query. ''' sortname = None if request.GET.get('sidx', ''): # 1) Sorting order specified on the request sortname = "%s %s" % (request.GET['sidx'], request.GET.get('sord', 'asc')) elif request.prefs: # 2) Sorting order from the preferences sortname = "%s %s" % (request.prefs.get( 'sidx', ''), request.GET.get('sord', 'asc')) if not sortname or sortname == " asc": # 3) Default sort order return query.order_by('key') else: # Validate the field does exist. # We only validate the first level field, and not the fields # on related models. sortargs = [] for s in sortname.split(","): stripped = s.strip() if not stripped: continue sortfield, direction = stripped.split(" ", 1) try: query.order_by(sortfield).query.__str__() if direction.strip() != "desc": sortargs.append(sortfield) else: sortargs.append('-%s' % sortfield) except: for r in reportclass.rows: if r.name == sortfield: try: query.order_by(r.field_name).query.__str__() if direction.strip() != "desc": sortargs.append(r.field_name) else: sortargs.append('-%s' % r.field_name) except: # Can't sort on this field pass break if sortargs: return query.order_by( 'key', *sortargs ) # The extra ordering by the 'key' is only change with the default method else: return query.order_by( 'key' ) # The extra ordering by the 'key' is only change with the default method @staticmethod def query(request, basequery, sortsql='1 asc'): basesql, baseparams = basequery.query.get_compiler( basequery.db).as_sql(with_col_aliases=False) # Build the query query = ''' with combinations as (%s) select row_to_json(data) from ( select -- Key field combinations.key as key, -- Attribute fields of item, location and supplier combinations.item_id as item, item.description as item__description, item.category as item__category, item.subcategory as item__subcategory, item.cost as item__cost, item.owner_id as item__owner, item.source as item__source, item.lastmodified as item__lastmodified, combinations.origin_id as origin, origin.description as origin__description, origin.category as origin__category, origin.subcategory as origin__subcategory, origin.available_id as origin__available, origin.lastmodified as origin__lastmodified, combinations.destination_id as destination, destination.description as destination__description, destination.category as destination__category, destination.subcategory as destination__subcategory, destination.available_id as destination__available, destination.lastmodified as destination__lastmodified, -- Buckets res.bucket as bucket, to_char(res.startdate, 'YYYY-MM-DD') as startdate, to_char(res.enddate, 'YYYY-MM-DD') as enddate, -- Values res.proposed_start as proposed_start, res.total_start as total_start, res.proposed_end as proposed_end, res.total_end as total_end, res.proposed_in_transit as proposed_in_transit, res.total_in_transit as total_in_transit from combinations inner join item on combinations.item_id = item.name left outer join location origin on combinations.origin_id = origin.name left outer join location destination on combinations.destination_id = destination.name inner join ( select operationplan.item_id, operationplan.origin_id, operationplan.destination_id, d.bucket, d.startdate, d.enddate, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) proposed_start, coalesce(sum( case when d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) total_start, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate <= operationplan.enddate and d.enddate > operationplan.enddate then operationplan.quantity else 0 end ), 0) proposed_end, coalesce(sum( case when d.startdate <= operationplan.enddate and d.enddate > operationplan.enddate then operationplan.quantity else 0 end ), 0) total_end, coalesce(sum( case when operationplan.status = 'proposed' and d.enddate > operationplan.startdate and d.enddate <= operationplan.enddate then operationplan.quantity else 0 end ), 0) proposed_in_transit, coalesce(sum( case when d.enddate > operationplan.startdate and d.enddate <= operationplan.enddate then operationplan.quantity else 0 end ), 0) total_in_transit from operationplan inner join combinations on operationplan.item_id = combinations.item_id and operationplan.origin_id = combinations.origin_id and operationplan.destination_id = combinations.destination_id cross join ( select name as bucket, startdate, enddate from common_bucketdetail where bucket_id = '%s' and enddate > '%s' and startdate < '%s' ) d where operationplan.type = 'DO' group by operationplan.item_id, operationplan.origin_id, operationplan.destination_id, d.bucket, d.startdate, d.enddate ) res on res.item_id = combinations.item_id and res.origin_id = combinations.origin_id and res.destination_id = combinations.destination_id order by %s, res.startdate ) data ''' % (basesql, request.report_bucket, request.report_startdate, request.report_enddate, sortsql) # Convert the SQL results to Python with connections[request.database].chunked_cursor() as cursor_chunked: cursor_chunked.execute(query, baseparams) for row in cursor_chunked: yield row[0]
class DetailReport(GridReport): ''' A list report to show OperationPlanResources. ''' template = 'input/operationplanreport.html' title = _("Resource detail report") model = OperationPlanResource permissions = (("view_resource_report", "Can view resource report"),) frozenColumns = 3 editable = False multiselect = False height = 250 help_url = 'user-guide/user-interface/plan-analysis/resource-detail-report.html' @ classmethod def basequeryset(reportclass, request, args, kwargs): if args and args[0]: try: res = Resource.objects.using(request.database).get(name__exact=args[0]) base = OperationPlanResource.objects.filter(resource__lft__gte=res.lft, resource__rght__lte=res.rght) except OperationPlanResource.DoesNotExist: base = OperationPlanResource.objects.filter(resource__exact=args[0]) else: base = OperationPlanResource.objects return base.select_related().extra(select={ 'pegging': "(select string_agg(value || ' : ' || key, ', ') from (select key, value from jsonb_each_text(plan->'pegging') order by key desc) peg)", 'opplan_duration': "(operationplan.enddate - operationplan.startdate)", 'setup_end': "(operationplan.plan->>'setupend')", 'setup_duration': "(operationplan.plan->>'setup')" }) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 # Adding custom operation attributes for f in getAttributeFields(Operation, related_name_prefix="operationplan__operation"): f.editable = False reportclass.rows += (f,) # Adding custom resource attributes for f in getAttributeFields(Resource, related_name_prefix="resource"): f.editable = False reportclass.rows += (f,) @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session['lasttab'] = 'plandetail' return { 'active_tab': 'plandetail', 'model': Resource, 'title': force_text(Resource._meta.verbose_name) + " " + args[0], 'post_title': _('plan detail') } else: return {'active_tab': 'plandetail', 'model': None} rows = ( GridFieldInteger('id', title='internal id', key=True, editable=False, hidden=True), GridFieldText('resource', title=_('resource'), field_name='resource__name', editable=False, formatter='detail', extra='"role":"input/resource"'), GridFieldInteger('operationplan__id', title=_('identifier'), editable=False), GridFieldText('operationplan__reference', title=_('reference'), editable=False), GridFieldText('operationplan__color', title=_('inventory status'), formatter='color', width='125', editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"'), GridFieldText('operationplan__operation__item', title=_('item'), editable=False, formatter='detail', extra='"role":"input/item"'), GridFieldText('operationplan__operation__location', title=_('location'), editable=False, formatter='detail', extra='"role":"input/location"'), GridFieldText('operationplan__operation__name', title=_('operation'), editable=False, formatter='detail', extra='"role":"input/operation"'), GridFieldText('operationplan__operation__description', title=string_concat(_('operation'), ' - ', _('description')), editable=False, initially_hidden=True), GridFieldText('operationplan__operation__category', title=string_concat(_('operation'), ' - ', _('category')), editable=False, initially_hidden=True), GridFieldText('operationplan__operation__subcategory', title=string_concat(_('operation'), ' - ', _('subcategory')), editable=False, initially_hidden=True), GridFieldText('operationplan__operation__type', title=string_concat(_('operation'), ' - ', _('type')), initially_hidden=True), GridFieldDuration('operationplan__operation__duration', title=string_concat(_('operation'), ' - ', _('duration')), initially_hidden=True), GridFieldDuration('operationplan__operation__duration_per', title=string_concat(_('operation'), ' - ', _('duration per unit')), initially_hidden=True), GridFieldDuration('operationplan__operation__fence', title=string_concat(_('operation'), ' - ', _('release fence')), initially_hidden=True), GridFieldDuration('operationplan__operation__posttime', title=string_concat(_('operation'), ' - ', _('post-op time')), initially_hidden=True), GridFieldNumber('operationplan__operation__sizeminimum', title=string_concat(_('operation'), ' - ', _('size minimum')), initially_hidden=True), GridFieldNumber('operationplan__operation__sizemultiple', title=string_concat(_('operation'), ' - ', _('size multiple')), initially_hidden=True), GridFieldNumber('operationplan__operation__sizemaximum', title=string_concat(_('operation'), ' - ', _('size maximum')), initially_hidden=True), GridFieldInteger('operationplan__operation__priority', title=string_concat(_('operation'), ' - ', _('priority')), initially_hidden=True), GridFieldDateTime('operationplan__operation__effective_start', title=string_concat(_('operation'), ' - ', _('effective start')), initially_hidden=True), GridFieldDateTime('operationplan__operation__effective_end', title=string_concat(_('operation'), ' - ', _('effective end')), initially_hidden=True), GridFieldCurrency('operationplan__operation__cost', title=string_concat(_('operation'), ' - ', _('cost')), initially_hidden=True), GridFieldText('operationplan__operation__search', title=string_concat(_('operation'), ' - ', _('search mode')), initially_hidden=True), GridFieldText('operationplan__operation__source', title=string_concat(_('operation'), ' - ', _('source')), initially_hidden=True), GridFieldLastModified('operationplan__operation__lastmodified', title=string_concat(_('operation'), ' - ', _('last modified')), initially_hidden=True), GridFieldDateTime('operationplan__startdate', title=_('start date'), editable=False, extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"min"'), GridFieldDateTime('operationplan__enddate', title=_('end date'), editable=False, extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"max"'), GridFieldDuration('opplan_duration', title=_('duration'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"'), GridFieldNumber('operationplan__quantity', title=_('quantity'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"'), GridFieldText('operationplan__status', title=_('status'), editable=False), GridFieldNumber('operationplan__criticality', title=_('criticality'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"'), GridFieldDuration('operationplan__delay', title=_('delay'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"'), GridFieldText('pegging', title=_('demands'), formatter='demanddetail', extra='"role":"input/demand"', width=300, editable=False, sortable=False), GridFieldText('operationplan__type', title=_('type'), field_name='operationplan__type', editable=False), GridFieldNumber('quantity', title=_('load quantity'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"'), GridFieldText('setup', title=_('setup'), editable=False, initially_hidden=True), GridFieldDateTime('setup_end', title=_('setup end date'), editable=False, initially_hidden=True), GridFieldDuration('setup_duration', title=_('setup duration'), editable=False, initially_hidden=True), # Optional fields referencing the item GridFieldText( 'operationplan__operation__item__description', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('description')) ), GridFieldText( 'operationplan__operation__item__category', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('category')) ), GridFieldText( 'operationplan__operation__item__subcategory', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('subcategory')) ), GridFieldText( 'operationplan__operation__item__owner', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('owner')), field_name='operationplan__operation__item__owner__name' ), GridFieldText( 'operationplan__operation__item__source', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('source')) ), GridFieldLastModified( 'operationplan__operation__item__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('last modified')), ), # Optional fields referencing the operation location GridFieldText( 'operationplan__operation__location__description', initially_hidden=True, editable=False, title=string_concat(_('location'), ' - ', _('description')) ), GridFieldText( 'operationplan__operation__location__category', title=string_concat(_('location'), ' - ', _('category')), initially_hidden=True, editable=False ), GridFieldText( 'operationplan__operation__location__subcategory', title=string_concat(_('location'), ' - ', _('subcategory')), initially_hidden=True, editable=False ), GridFieldText( 'operationplan__operation__location__available', editable=False, title=string_concat(_('location'), ' - ', _('available')), initially_hidden=True, field_name='operationplan__operation__location__available__name', formatter='detail', extra='"role":"input/calendar"' ), GridFieldText( 'operationplan__operation__location__owner', initially_hidden=True, title=string_concat(_('location'), ' - ', _('owner')), field_name='operationplan__operation__location__owner__name', formatter='detail', extra='"role":"input/location"', editable=False ), GridFieldText( 'operationplan__operation__location__source', initially_hidden=True, editable=False, title=string_concat(_('location'), ' - ', _('source')) ), GridFieldLastModified( 'operationplan__operation__location__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('location'), ' - ', _('last modified')) ), # Optional fields referencing the resource GridFieldText( 'resource__description', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('description')) ), GridFieldText( 'resource__category', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('category')) ), GridFieldText( 'resource__subcategory', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('subcategory')) ), GridFieldText( 'resource__type', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('type')) ), GridFieldNumber( 'resource__maximum', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('maximum')) ), GridFieldText( 'resource__maximum_calendar', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('maximum calendar')), field_name='resource__maximum_calendar__name', formatter='detail', extra='"role":"input/calendar"' ), GridFieldCurrency( 'resource__cost', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('cost')) ), GridFieldDuration( 'resource__maxearly', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('maxearly')) ), GridFieldText( 'resource__setupmatrix', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('setupmatrix')), field_name='resource__setupmatrix__name', formatter='detail', extra='"role":"input/setupmatrix"' ), GridFieldText( 'resource__setup', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('setup')) ), GridFieldText( 'resource_location', editable=False, initially_hidden=True, title=string_concat(_('resource'), ' - ', _('location')), field_name='resource__location__name', formatter='detail', extra='"role":"input/location"' ), # Optional fields referencing the resource location GridFieldText( 'resource__location__description', initially_hidden=True, editable=False, title=string_concat(_('resource'), ' - ', _('location'), ' - ', _('description')) ), GridFieldText( 'resource__location__category', initially_hidden=True, editable=False, title=string_concat(_('resource'), ' - ', _('location'), ' - ', _('category')) ), GridFieldText( 'resource__location__subcategory', initially_hidden=True, editable=False, title=string_concat(_('resource'), ' - ', _('location'), ' - ', _('subcategory')) ), GridFieldText( 'resource__location__available', initially_hidden=True, editable=False, title=string_concat(_('resource'), ' - ', _('location'), ' - ', _('available')), field_name='resource__location__available__name', formatter='detail', extra='"role":"input/calendar"' ), GridFieldText( 'resource__location__owner', extra='"role":"input/location"', editable=False, title=string_concat(_('resource'), ' - ', _('location'), ' - ', _('owner')), initially_hidden=True, field_name='resource__location__owner__name', formatter='detail' ), GridFieldText( 'resource__location__source', initially_hidden=True, editable=False, title=string_concat(_('resource'), ' - ', _('location'), ' - ', _('source')) ), GridFieldLastModified( 'resource__location__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('resource'), ' - ', _('location'), ' - ', _('last modified')) ), )
class DistributionReport(GridPivot): ''' A report summarizing all distribution orders. ''' template = 'output/distribution_order_summary.html' title = _('Distribution order summary') model = DistributionOrder help_url = 'user-guide/user-interface/plan-analysis/distribution-order-summary.html' rows = ( GridFieldText('key', key=True, search=False, initially_hidden=True, hidden=True, field_name="item__name", editable=False), GridFieldText('item', title=_('item'), editable=False, field_name='item__name', formatter='detail', extra='"role":"input/item"'), GridFieldText('item__description', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('description'))), GridFieldText('item__category', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('category'))), GridFieldText('item__subcategory', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('subcategory'))), GridFieldCurrency('item__cost', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('cost')), field_name='item__cost'), GridFieldText('item__owner', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('owner')), field_name='item__owner__name'), GridFieldText('item__source', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('source'))), GridFieldLastModified('item__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('last modified'))), GridFieldText('origin', title=_('origin'), editable=False, field_name='origin__name', formatter='detail', extra='"role":"input/location"'), GridFieldText('origin__description', editable=False, initially_hidden=True, title=string_concat(_('origin'), ' - ', _('description'))), GridFieldText('origin__category', editable=False, initially_hidden=True, title=string_concat(_('origin'), ' - ', _('category'))), GridFieldText('origin__subcategory', editable=False, initially_hidden=True, title=string_concat(_('origin'), ' - ', _('subcategory'))), GridFieldText('origin__available', editable=False, initially_hidden=True, title=string_concat(_('origin'), ' - ', _('available')), field_name='origin__available__name', formatter='detail', extra='"role":"input/calendar"'), GridFieldLastModified('origin__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('origin'), ' - ', _('last modified'))), GridFieldText('destination', title=_('destination'), editable=False, field_name='destination__name', formatter='detail', extra='"role":"input/location"'), GridFieldText('destination__description', editable=False, initially_hidden=True, title=string_concat(_('destination'), ' - ', _('description'))), GridFieldText('destination__category', editable=False, initially_hidden=True, title=string_concat(_('destination'), ' - ', _('category'))), GridFieldText('destination__subcategory', editable=False, initially_hidden=True, title=string_concat(_('destination'), ' - ', _('subcategory'))), GridFieldText('destination__available', editable=False, initially_hidden=True, title=string_concat(_('destination'), ' - ', _('available')), field_name='destination__available__name', formatter='detail', extra='"role":"input/calendar"'), GridFieldLastModified('destination__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('destination'), ' - ', _('last modified'))), ) crosses = ( ('proposed_start', { 'title': _('proposed shipping') }), ('total_start', { 'title': _('total shipping') }), ('proposed_end', { 'title': _('proposed receiving') }), ('total_end', { 'title': _('total receiving') }), ('proposed_in_transit', { 'title': _('proposed in transit') }), ('total_in_transit', { 'title': _('total in transit') }), ) @staticmethod def basequeryset(request, *args, **kwargs): current, start, end = getHorizon(request) return DistributionOrder.objects.all() \ .filter(startdate__lte=end, enddate__gte=start) \ .distinct('item', 'origin', 'destination') \ .order_by() # Ordering isn't compatible with the distinct @staticmethod def query(request, basequery, sortsql='1 asc'): basesql, baseparams = basequery.query.get_compiler( basequery.db).as_sql(with_col_aliases=False) # Build the query query = ''' with combinations as (%s) select row_to_json(data) from ( select -- Key field combinations.item_id || combinations.origin_id || combinations.destination_id as key, -- Attribute fields of item, location and supplier combinations.item_id as item, item.description as item__description, item.category as item__category, item.subcategory as item__subcategory, item.cost as item__cost, item.owner_id as item__owner, item.source as item__source, item.lastmodified as item__lastmodified, combinations.origin_id as origin, origin.description as origin__description, origin.category as origin__category, origin.subcategory as origin__subcategory, origin.available_id as origin__available, origin.lastmodified as origin__lastmodified, combinations.destination_id as destination, destination.description as destination__description, destination.category as destination__category, destination.subcategory as destination__subcategory, destination.available_id as destination__available, destination.lastmodified as destination__lastmodified, -- Buckets res.bucket as bucket, to_char(res.startdate, 'YYYY-MM-DD') as startdate, to_char(res.enddate, 'YYYY-MM-DD') as enddate, -- Values res.proposed_start as proposed_start, res.total_start as total_start, res.proposed_end as proposed_end, res.total_end as total_end, res.proposed_in_transit as proposed_in_transit, res.total_in_transit as total_in_transit from combinations inner join item on combinations.item_id = item.name left outer join location origin on combinations.origin_id = origin.name left outer join location destination on combinations.destination_id = destination.name inner join ( select operationplan.item_id, operationplan.origin_id, operationplan.destination_id, d.bucket, d.startdate, d.enddate, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) proposed_start, coalesce(sum( case when d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) total_start, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate <= operationplan.enddate and d.enddate > operationplan.enddate then operationplan.quantity else 0 end ), 0) proposed_end, coalesce(sum( case when d.startdate <= operationplan.enddate and d.enddate > operationplan.enddate then operationplan.quantity else 0 end ), 0) total_end, coalesce(sum( case when operationplan.status = 'proposed' and d.enddate > operationplan.startdate and d.enddate <= operationplan.enddate then operationplan.quantity else 0 end ), 0) proposed_in_transit, coalesce(sum( case when d.enddate > operationplan.startdate and d.enddate <= operationplan.enddate then operationplan.quantity else 0 end ), 0) total_in_transit from operationplan inner join combinations on operationplan.item_id = combinations.item_id and operationplan.origin_id = combinations.origin_id and operationplan.destination_id = combinations.destination_id cross join ( select name as bucket, startdate, enddate from common_bucketdetail where bucket_id = '%s' and enddate > '%s' and startdate < '%s' ) d where operationplan.type = 'DO' group by operationplan.item_id, operationplan.origin_id, operationplan.destination_id, d.bucket, d.startdate, d.enddate ) res on res.item_id = combinations.item_id and res.origin_id = combinations.origin_id and res.destination_id = combinations.destination_id order by %s, res.startdate ) data ''' % (basesql, request.report_bucket, request.report_startdate, request.report_enddate, sortsql) # Convert the SQL results to Python with connections[request.database].chunked_cursor() as cursor_chunked: cursor_chunked.execute(query, baseparams) for row in cursor_chunked: yield row[0]
class OverviewReport(GridPivot): ''' A report summarizing all manufacturing orders. ''' template = 'output/operation.html' title = _('Manufacturing order summary') model = Operation permissions = (("view_operation_report", "Can view operation report"), ) help_url = 'user-guide/user-interface/plan-analysis/manufacturing-order-summary.html' rows = ( GridFieldText('operation', title=_('operation'), key=True, editable=False, field_name='name', formatter='detail', extra='"role":"input/operation"'), GridFieldText('location', title=_('location'), editable=False, field_name='location__name', formatter='detail', extra='"role":"input/location"'), # Optional fields on the operation GridFieldText('item', title=_('item'), editable=False, field_name="item__name", formatter='detail', extra='"role":"input/item"', initially_hidden=True), GridFieldText('description', title=_('description'), editable=False, initially_hidden=True), GridFieldText('category', title=_('category'), editable=False, initially_hidden=True), GridFieldText('subcategory', title=_('subcategory'), editable=False, initially_hidden=True), GridFieldText('type', title=_('type'), initially_hidden=True, editable=False), GridFieldDuration('duration', title=_('duration'), initially_hidden=True, editable=False), GridFieldDuration('duration_per', title=_('duration per unit'), initially_hidden=True, editable=False), GridFieldDuration('fence', title=_('release fence'), initially_hidden=True, editable=False), GridFieldDuration('posttime', title=_('post-op time'), initially_hidden=True, editable=False), GridFieldNumber('sizeminimum', title=_('size minimum'), initially_hidden=True, editable=False), GridFieldNumber('sizemultiple', title=_('size multiple'), initially_hidden=True, editable=False), GridFieldNumber('sizemaximum', title=_('size maximum'), initially_hidden=True, editable=False), GridFieldInteger('priority', title=_('priority'), initially_hidden=True, editable=False), GridFieldDateTime('effective_start', title=_('effective start'), initially_hidden=True, editable=False), GridFieldDateTime('effective_end', title=_('effective end'), initially_hidden=True, editable=False), GridFieldCurrency('cost', title=_('cost'), initially_hidden=True, editable=False), GridFieldText('search', title=_('search mode'), initially_hidden=True, editable=False), GridFieldText('source', title=_('source'), initially_hidden=True, editable=False), GridFieldLastModified('lastmodified', initially_hidden=True, editable=False), # Optional fields on the location GridFieldText('location__description', editable=False, initially_hidden=True, title=string_concat(_('location'), ' - ', _('description'))), GridFieldText('location__category', editable=False, initially_hidden=True, title=string_concat(_('location'), ' - ', _('category'))), GridFieldText('location__subcategory', editable=False, initially_hidden=True, title=string_concat(_('location'), ' - ', _('subcategory'))), GridFieldText('location__available', editable=False, initially_hidden=True, title=string_concat(_('location'), ' - ', _('available')), field_name='location__available__name', formatter='detail', extra='"role":"input/calendar"'), GridFieldLastModified('location__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('location'), ' - ', _('last modified'))), # Optional fields referencing the item GridFieldText('item__description', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('description'))), GridFieldText('item__category', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('category'))), GridFieldText('item__subcategory', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('subcategory'))), GridFieldText('item__owner', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('owner')), field_name='item__owner__name'), GridFieldText('item__source', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('source'))), GridFieldLastModified('item__lastmodified', initially_hidden=True, editable=False, title=string_concat(_('item'), ' - ', _('last modified'))), ) crosses = ( ('proposed_start', { 'title': _('proposed starts') }), ('total_start', { 'title': _('total starts') }), ('proposed_end', { 'title': _('proposed ends') }), ('total_end', { 'title': _('total ends') }), ('production_proposed', { 'title': _('proposed production') }), ('production_total', { 'title': _('total production') }), ) @staticmethod def basequeryset(request, *args, **kwargs): if args and args[0]: request.session['lasttab'] = 'plan' return Operation.objects.all() else: current, start, end = getHorizon(request) return Operation.objects.all().extra(where=[ 'exists (select 1 from operationplan where operationplan.operation_id = operation.name and startdate <= %s and enddate >= %s)' ], params=[end, start]) @staticmethod def extra_context(request, *args, **kwargs): if args and args[0]: request.session['lasttab'] = 'plan' return { 'title': force_text(Operation._meta.verbose_name) + " " + args[0], 'post_title': _('plan') } else: return {} @staticmethod def query(request, basequery, sortsql='1 asc'): basesql, baseparams = basequery.query.get_compiler( basequery.db).as_sql(with_col_aliases=False) # Build the query query = ''' select operation.name, location.name, operation.item_id, operation.description, operation.category, operation.subcategory, operation.type, operation.duration, operation.duration_per, operation.fence, operation.posttime, operation.sizeminimum, operation.sizemultiple, operation.sizemaximum, operation.priority, operation.effective_start, operation.effective_end, operation.cost, operation.search, operation.source, operation.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.lastmodified, item.description, item.category, item.subcategory, item.owner_id, item.source, item.lastmodified, res.bucket, res.startdate, res.enddate, res.proposed_start, res.total_start, res.proposed_end, res.total_end, res.proposed_production, res.total_production from operation left outer join item on operation.item_id = item.name left outer join location on operation.location_id = location.name inner join ( select oper.name as operation_id, d.bucket, d.startdate, d.enddate, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) proposed_start, coalesce(sum( case when d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) total_start, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate < operationplan.enddate and d.enddate >= operationplan.enddate then operationplan.quantity else 0 end ), 0) proposed_end, coalesce(sum( case when d.startdate < operationplan.enddate and d.enddate >= operationplan.enddate then operationplan.quantity else 0 end ), 0) total_end, coalesce(sum( case when operationplan.status = 'proposed' then ( -- Total overlap extract (epoch from least(operationplan.enddate, d.enddate) - greatest(operationplan.startdate, d.startdate)) -- Minus the interruptions - coalesce(( select sum(greatest(0, extract (epoch from least(to_timestamp(value->>1, 'YYYY-MM-DD HH24:MI:SS'), d.enddate) - greatest(to_timestamp(value->>0, 'YYYY-MM-DD HH24:MI:SS'), d.startdate) ))) from ( select * from jsonb_array_elements(plan->'interruptions')) breaks ), 0) ) / greatest(1, extract(epoch from operationplan.enddate - operationplan.startdate) - coalesce((plan#>>'{unavailable}')::numeric, 0)) * operationplan.quantity else 0 end ), 0) proposed_production, coalesce(sum( ( -- Total overlap extract (epoch from least(operationplan.enddate, d.enddate) - greatest(operationplan.startdate, d.startdate)) -- Minus the interruptions - coalesce(( select sum(greatest(0, extract (epoch from least(to_timestamp(value->>1, 'YYYY-MM-DD HH24:MI:SS'), d.enddate) - greatest(to_timestamp(value->>0, 'YYYY-MM-DD HH24:MI:SS'), d.startdate) ))) from ( select * from jsonb_array_elements(plan->'interruptions')) breaks ), 0) ) / greatest(1, extract(epoch from operationplan.enddate - operationplan.startdate) - coalesce((plan#>>'{unavailable}')::numeric, 0)) * operationplan.quantity ), 0) total_production from (%s) oper -- 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 -- Match overlapping operationplans left outer join operationplan on operationplan.operation_id = oper.name and (operationplan.startdate, operationplan.enddate) overlaps (d.startdate, d.enddate) group by oper.name, d.bucket, d.startdate, d.enddate ) res on res.operation_id = operation.name order by %s, res.startdate ''' % (basesql, request.report_bucket, request.report_startdate, request.report_enddate, sortsql) # Convert the SQl results to Python with connections[request.database].chunked_cursor() as cursor_chunked: cursor_chunked.execute(query, baseparams) for row in cursor_chunked: yield { 'operation': row[0], 'location': row[1], 'item': row[2], 'description': row[3], 'category': row[4], 'subcategory': row[5], 'type': row[6], 'duration': row[7], 'duration_per': row[8], 'fence': row[9], 'posttime': row[10], 'sizeminimum': row[11], 'sizemultiple': row[12], 'sizemaximum': row[13], 'priority': row[14], 'effective_start': row[15], 'effective_end': row[16], 'cost': row[17], 'search': row[18], 'source': row[19], 'lastmodified': row[20], 'location__description': row[21], 'location__category': row[22], 'location__subcategory': row[23], 'location__available': row[24], 'location__lastmodified': row[25], 'item__description': row[26], 'item__category': row[27], 'item__subcategory': row[28], 'item__owner': row[29], 'item__source': row[30], 'item__lastmodified': row[31], 'bucket': row[32], 'startdate': row[33].date(), 'enddate': row[34].date(), 'proposed_start': row[35], 'total_start': row[36], 'proposed_end': row[37], 'total_end': row[38], 'production_proposed': row[39], 'production_total': row[40] }
class PurchaseOrderList(OperationPlanMixin, GridReport): template = "input/operationplanreport.html" title = _("purchase orders") model = PurchaseOrder default_sort = (1, "desc") frozenColumns = 1 multiselect = True editable = True height = 250 help_url = "modeling-wizard/purchasing/purchase-orders.html" message_when_empty = Template( """ <h3>Define purchase orders</h3> <br> This table defines ongoing and proposed purchase orders.<br><br> Use this table to load ongoing purchase orders in the status "confirmed".<br><br> The planning algorithm will further populate this table with additional "proposed" purchase orders for the future.<br> <br><br> <div role="group" class="btn-group.btn-group-justified"> <a href="{{request.prefix}}/data/input/purchaseorder/add/" onclick="window.location = $(event.target).attr('href')" class="btn btn-primary">Create a single purchase order<br>in a form</a> <a href="{{request.prefix}}/wizard/load/production/?currentstep=7" onclick="window.location = $(event.target).attr('href')" class="btn btn-primary">Wizard to upload purchase orders<br>from a spreadsheet</a> </div> <br> """ ) calendarmode = "start_end" @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session["lasttab"] = "purchaseorders" paths = request.path.split("/") path = paths[4] if path == "supplier" or request.path.startswith("/detail/input/supplier/"): return { "default_operationplan_type": "PO", "groupBy": "status", "active_tab": "purchaseorders", "model": Supplier, "title": force_text(Supplier._meta.verbose_name) + " " + args[0], "post_title": _("purchase orders"), } elif path == "location" or request.path.startswith( "/detail/input/location/" ): return { "default_operationplan_type": "PO", "groupBy": "status", "active_tab": "purchaseorders", "model": Location, "title": force_text(Location._meta.verbose_name) + " " + args[0], "post_title": _("purchase orders"), } elif path == "item" or request.path.startswith("/detail/input/item/"): return { "default_operationplan_type": "PO", "groupBy": "status", "active_tab": "purchaseorders", "model": Item, "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": _("purchase orders"), } elif path == "operationplanmaterial": return { "default_operationplan_type": "PO", "groupBy": "status", "active_tab": "purchaseorders", "model": Item, "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": force_text( _("on order in %(loc)s at %(date)s") % {"loc": args[1], "date": args[2]} ), } elif path == "produced": return { "default_operationplan_type": "PO", "groupBy": "status", "active_tab": "purchaseorders", "model": Item, "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": force_text( _("on order in %(loc)s between %(date1)s and %(date2)s") % {"loc": args[1], "date1": args[2], "date2": args[3]} ), } else: return { "default_operationplan_type": "PO", "groupBy": "status", "active_tab": "edit", "model": Item, } elif "parentreference" in request.GET: return { "default_operationplan_type": "PO", "groupBy": "status", "active_tab": "edit", "title": force_text(PurchaseOrder._meta.verbose_name) + " " + request.GET["parentreference"], } else: return { "default_operationplan_type": "PO", "groupBy": "status", "active_tab": "purchaseorders", } @classmethod 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(), ), ) rows = ( GridFieldText( "reference", title=_("reference"), key=True, formatter="detail", extra='role:"input/purchaseorder"', editable=not settings.ERP_CONNECTOR, ), GridFieldNumber( "computed_color", title=_("inventory status"), formatter="color", width="125", editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldNumber("color", hidden=True), GridFieldHierarchicalText( "item", title=_("item"), field_name="item__name", formatter="detail", extra='"role":"input/item"', model=Item, ), GridFieldHierarchicalText( "location", title=_("location"), field_name="location__name", formatter="detail", extra='"role":"input/location"', model=Location, ), GridFieldHierarchicalText( "supplier", title=_("supplier"), field_name="supplier__name", formatter="detail", extra='"role":"input/supplier"', model=Supplier, ), GridFieldDateTime( "startdate", title=_("ordering date"), extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"min"', ), GridFieldDateTime( "enddate", title=_("receipt date"), extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"max"', ), GridFieldNumber( "quantity", title=_("quantity"), extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldChoice( "status", title=_("status"), choices=PurchaseOrder.orderstatus, editable=not settings.ERP_CONNECTOR, ), GridFieldCurrency( "unit_cost", title=format_lazy("{} - {}", _("item"), _("cost")), editable=False, search=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"', ), GridFieldCurrency( "total_cost", title=_("total cost"), editable=False, search=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldNumber( "total_volume", title=_("total volume"), editable=False, search=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldNumber( "total_weight", title=_("total weight"), editable=False, search=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldText( "batch", title=_("batch"), editable="true", initially_hidden=True ), GridFieldNumber( "criticality", title=_("criticality"), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldDuration( "delay", title=_("delay"), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"', ), GridFieldJSON( "demands", title=_("demands"), editable=False, search=True, sortable=False, formatter="demanddetail", extra='"role":"input/demand"', ), GridFieldText("source", title=_("source"), initially_hidden=True), GridFieldBool( "feasible", title=_("feasible"), editable=False, initially_hidden=True, search=True, ), GridFieldLastModified("lastmodified"), # Annoted fields referencing the itemsupplier GridFieldNumber( "itemsupplier_sizeminimum", title=format_lazy("{} - {}", _("item supplier"), _("size minimum")), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldNumber( "itemsupplier_sizemultiple", title=format_lazy("{} - {}", _("item supplier"), _("size multiple")), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldNumber( "itemsupplier_sizemaximum", title=format_lazy("{} - {}", _("item supplier"), _("size maximum")), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldDateTime( "itemsupplier_effective_end", title=format_lazy("{} - {}", _("item supplier"), _("effective end")), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldDateTime( "itemsupplier_effective_start", title=format_lazy("{} - {}", _("item supplier"), _("effective start")), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldNumber( "itemsupplier_priority", title=format_lazy("{} - {}", _("item supplier"), _("priority")), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), # Optional fields referencing the item GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the supplier GridFieldText( "supplier__description", title=format_lazy("{} - {}", _("supplier"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "supplier__category", title=format_lazy("{} - {}", _("supplier"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "supplier__subcategory", title=format_lazy("{} - {}", _("supplier"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "supplier__owner", title=format_lazy("{} - {}", _("supplier"), _("owner")), initially_hidden=True, field_name="supplier__owner__name", formatter="detail", extra='"role":"input/supplier"', editable=False, ), GridFieldText( "supplier__source", title=format_lazy("{} - {}", _("supplier"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "supplier__lastmodified", title=format_lazy("{} - {}", _("supplier"), _("last modified")), initially_hidden=True, editable=False, ), GridFieldText( "end_items", title=_("end items"), editable=False, search=False, sortable=False, initially_hidden=True, formatter="listdetail", extra='"role":"input/item"', ), ) if settings.ERP_CONNECTOR: actions = [ { "name": "erp_incr_export", "label": format_lazy("export to {erp}", erp=settings.ERP_CONNECTOR), "function": "ERPconnection.IncrementalExport(jQuery('#grid'),'PO')", } ] else: actions = [ { "name": "proposed", "label": format_lazy( _("change status to {status}"), status=_("proposed") ), "function": "grid.setStatus('proposed')", }, { "name": "approved", "label": format_lazy( _("change status to {status}"), status=_("approved") ), "function": "grid.setStatus('approved')", }, { "name": "confirmed", "label": format_lazy( _("change status to {status}"), status=_("confirmed") ), "function": "grid.setStatus('confirmed')", }, { "name": "completed", "label": format_lazy( _("change status to {status}"), status=_("completed") ), "function": "grid.setStatus('completed')", }, { "name": "closed", "label": format_lazy( _("change status to {status}"), status=_("closed") ), "function": "grid.setStatus('closed')", }, ] @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 for f in getAttributeFields(PurchaseOrder): reportclass.rows += (f,) for f in getAttributeFields(Item, related_name_prefix="item"): f.editable = False reportclass.rows += (f,) for f in getAttributeFields(Location, related_name_prefix="location"): f.editable = False reportclass.rows += (f,) for f in getAttributeFields(Supplier, related_name_prefix="supplier"): f.editable = False reportclass.rows += (f,)
class ItemSupplierList(GridReport): title = _("item suppliers") basequeryset = ItemSupplier.objects.all() model = ItemSupplier frozenColumns = 1 help_url = "modeling-wizard/purchasing/item-suppliers.html" message_when_empty = Template( """ <h3>Define item suppliers</h3> <br> This table defines which items can be procured from which supplier.<br> <br><br> <div role="group" class="btn-group.btn-group-justified"> <a href="{{request.prefix}}/data/input/itemsupplier/add/" class="btn btn-primary">Create a single item supplier<br>in a form</a> <a href="{{request.prefix}}/wizard/load/production/?currentstep=4" class="btn btn-primary">Wizard to upload item suppliers<br>from a spreadsheet</a> </div> <br> """ ) rows = ( GridFieldInteger( "id", title=_("identifier"), key=True, formatter="detail", extra='"role":"input/itemsupplier"', initially_hidden=True, ), GridFieldHierarchicalText( "item", title=_("item"), field_name="item__name", formatter="detail", extra='"role":"input/item"', model=Item, ), GridFieldHierarchicalText( "location", title=_("location"), field_name="location__name", formatter="detail", extra='"role":"input/location"', model=Location, ), GridFieldText( "supplier", title=_("supplier"), field_name="supplier__name", formatter="detail", extra='"role":"input/supplier"', ), GridFieldDuration("leadtime", title=_("lead time")), GridFieldNumber("sizeminimum", title=_("size minimum")), GridFieldNumber("sizemultiple", title=_("size multiple")), GridFieldNumber("sizemaximum", title=_("size maximum"), initially_hidden=True), GridFieldCurrency("cost", title=_("cost")), GridFieldInteger("priority", title=_("priority")), GridFieldDuration("fence", title=_("fence"), initially_hidden=True), GridFieldDateTime( "effective_start", title=_("effective start"), initially_hidden=True ), GridFieldDateTime( "effective_end", title=_("effective end"), initially_hidden=True ), GridFieldText( "resource", title=_("resource"), field_name="resource__name", formatter="detail", extra='"role":"input/resource"', initially_hidden=True, ), GridFieldNumber( "resource_qty", title=_("resource quantity"), initially_hidden=True ), GridFieldText("source", title=_("source"), initially_hidden=True), GridFieldLastModified("lastmodified"), # Optional fields referencing the item GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldCurrency( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the supplier GridFieldText( "supplier__description", title=format_lazy("{} - {}", _("supplier"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "supplier__category", title=format_lazy("{} - {}", _("supplier"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "supplier__subcategory", title=format_lazy("{} - {}", _("supplier"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "supplier__available", title=format_lazy("{} - {}", _("supplier"), _("available")), field_name="supplier__available__name", formatter="detail", extra='"role":"input/calendar"', initially_hidden=True, editable=False, ), GridFieldText( "supplier__source", title=format_lazy("{} - {}", _("supplier"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "supplier__lastmodified", title=format_lazy("{} - {}", _("supplier"), _("last modified")), initially_hidden=True, editable=False, ), ) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 reportclass.attr_sql = "" # Adding custom item attributes for f in getAttributeFields( Item, related_name_prefix="item", initially_hidden=True ): reportclass.rows += (f,) reportclass.attr_sql += "item.%s, " % f.name.split("__")[-1] # Adding custom location attributes for f in getAttributeFields( Location, related_name_prefix="location", initially_hidden=True ): reportclass.rows += (f,) reportclass.attr_sql += "location.%s, " % f.name.split("__")[-1]
class DeliveryOrderList(GridReport): template = "input/deliveryorder.html" title = _("delivery orders") model = DeliveryOrder frozenColumns = 0 editable = True multiselect = True help_url = "model-reference/delivery-orders.html" rows = ( GridFieldText( "reference", title=_("reference"), key=True, formatter="detail", extra='role:"input/deliveryorder"', editable=not settings.ERP_CONNECTOR, ), GridFieldText("batch", title=_("batch"), editable="true", initially_hidden=True), GridFieldText( "demand", title=_("demand"), field_name="demand__name", formatter="detail", extra='"role":"input/demand"', ), GridFieldText( "item", title=_("item"), field_name="item__name", formatter="detail", extra='"role":"input/item"', ), GridFieldText( "customer", title=_("customer"), field_name="demand__customer__name", formatter="detail", extra='"role":"input/customer"', ), GridFieldText( "location", title=_("location"), field_name="location__name", formatter="detail", extra='"role":"input/location"', ), GridFieldNumber("quantity", title=_("quantity")), GridFieldNumber("demand__quantity", title=_("demand quantity"), editable=False), GridFieldDateTime("startdate", title=_("start date")), GridFieldDateTime( "enddate", title=_("end date"), extra=GridFieldDateTime.extra + ',"cellattr":enddatecellattr', ), GridFieldDateTime("due", field_name="demand__due", title=_("due date"), editable=False), GridFieldChoice( "status", title=_("status"), choices=OperationPlan.orderstatus, editable=not settings.ERP_CONNECTOR, ), GridFieldDuration( "delay", title=_("delay"), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"', ), # Optional fields referencing the item GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldCurrency( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the customer GridFieldText( "demand__customer__description", title=format_lazy("{} - {}", _("customer"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "demand__customer__category", title=format_lazy("{} - {}", _("customer"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "demand__customer__subcategory", title=format_lazy("{} - {}", _("customer"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "demand__customer__owner", title=format_lazy("{} - {}", _("customer"), _("owner")), initially_hidden=True, field_name="supplier__owner__name", formatter="detail", extra='"role":"input/supplier"', editable=False, ), GridFieldText( "demand__customer__source", title=format_lazy("{} - {}", _("customer"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "demand__customer__lastmodified", title=format_lazy("{} - {}", _("customer"), _("last modified")), initially_hidden=True, editable=False, ), ) @classmethod def basequeryset(reportclass, request, *args, **kwargs): q = DeliveryOrder.objects.all() # special keyword superop used for search field of operationplan if "parentreference" in request.GET: parentreference = request.GET["parentreference"] q = q.filter(reference=parentreference) if args and args[0]: path = request.path.split("/")[4] if path == "consumed": return q.filter( item__name=args[0], location__name=args[1], enddate__gte=args[2], enddate__lt=args[3], ) else: try: 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) return q @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session["lasttab"] = "deliveryorders" path = request.path.split("/")[4] if path == "consumed": return { "active_tab": "deliveryorders", "model": Item, "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": force_text( _("delivered from %(loc)s between %(date1)s and %(date2)s" ) % { "loc": args[1], "date1": args[2], "date2": args[3] }), } else: return { "active_tab": "deliveryorders", "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": _("delivery orders"), } else: return {"active_tab": "deliveryorders"} @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 for f in getAttributeFields(DeliveryOrder): reportclass.rows += (f, ) for f in getAttributeFields(Item, related_name_prefix="item"): f.editable = False f.initially_hidden = True reportclass.rows += (f, ) for f in getAttributeFields(Location, related_name_prefix="location"): f.editable = False f.initially_hidden = True reportclass.rows += (f, ) for f in getAttributeFields( Customer, related_name_prefix="demand__customer"): f.editable = False f.initially_hidden = True reportclass.rows += (f, )
class OverviewReport(GridPivot): """ A report summarizing all manufacturing orders. """ template = "output/operation.html" title = _("Manufacturing order summary") model = Operation permissions = (("view_operation_report", "Can view operation report"), ) help_url = "user-interface/plan-analysis/manufacturing-order-summary.html" rows = ( GridFieldText( "operation", title=_("operation"), key=True, editable=False, field_name="name", formatter="detail", extra='"role":"input/operation"', ), GridFieldText( "location", title=_("location"), editable=False, field_name="location__name", formatter="detail", extra='"role":"input/location"', ), # Optional fields on the operation GridFieldText( "item", title=_("item"), editable=False, field_name="item__name", formatter="detail", extra='"role":"input/item"', initially_hidden=True, ), GridFieldText("description", title=_("description"), editable=False, initially_hidden=True), GridFieldText("category", title=_("category"), editable=False, initially_hidden=True), GridFieldText("subcategory", title=_("subcategory"), editable=False, initially_hidden=True), GridFieldText("type", title=_("type"), initially_hidden=True, editable=False), GridFieldDuration("duration", title=_("duration"), initially_hidden=True, editable=False), GridFieldDuration( "duration_per", title=_("duration per unit"), initially_hidden=True, editable=False, ), GridFieldDuration("fence", title=_("release fence"), initially_hidden=True, editable=False), GridFieldDuration("posttime", title=_("post-op time"), initially_hidden=True, editable=False), GridFieldNumber( "sizeminimum", title=_("size minimum"), initially_hidden=True, editable=False, ), GridFieldNumber( "sizemultiple", title=_("size multiple"), initially_hidden=True, editable=False, ), GridFieldNumber( "sizemaximum", title=_("size maximum"), initially_hidden=True, editable=False, ), GridFieldInteger("priority", title=_("priority"), initially_hidden=True, editable=False), GridFieldDateTime( "effective_start", title=_("effective start"), initially_hidden=True, editable=False, ), GridFieldDateTime( "effective_end", title=_("effective end"), initially_hidden=True, editable=False, ), GridFieldCurrency("cost", title=_("cost"), initially_hidden=True, editable=False), GridFieldText("search", title=_("search mode"), initially_hidden=True, editable=False), GridFieldText("source", title=_("source"), initially_hidden=True, editable=False), GridFieldLastModified("lastmodified", initially_hidden=True, editable=False), # Optional fields on the location GridFieldText( "location__description", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("location"), _("description")), ), GridFieldText( "location__category", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("location"), _("category")), ), GridFieldText( "location__subcategory", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("location"), _("subcategory")), ), GridFieldText( "location__available", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("location"), _("available")), field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldLastModified( "location__lastmodified", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("location"), _("last modified")), ), # Optional fields referencing the item GridFieldText( "item__description", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("description")), ), GridFieldText( "item__category", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("category")), ), GridFieldText( "item__subcategory", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("subcategory")), ), GridFieldText( "item__owner", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", ), GridFieldText( "item__source", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("source")), ), GridFieldLastModified( "item__lastmodified", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("last modified")), ), ) crosses = ( ("proposed_start", { "title": _("proposed starts"), "initially_hidden": True }), ("total_start", { "title": _("total starts") }), ("proposed_end", { "title": _("proposed ends"), "initially_hidden": True }), ("total_end", { "title": _("total ends") }), ( "production_proposed", { "title": _("proposed production"), "initially_hidden": True }, ), ("production_total", { "title": _("total production") }), ) @staticmethod def basequeryset(request, *args, **kwargs): if args and args[0]: request.session["lasttab"] = "plan" return Operation.objects.filter(name=args[0]) else: current, start, end = getHorizon(request) return Operation.objects.all().extra( where=[ "exists (select 1 from operationplan where operationplan.operation_id = operation.name and startdate <= %s and enddate >= %s)" ], params=[end, start], ) @staticmethod def extra_context(request, *args, **kwargs): if args and args[0]: request.session["lasttab"] = "plan" return { "title": force_text(Operation._meta.verbose_name) + " " + args[0], "post_title": _("plan"), "model": Operation, } else: return {} @staticmethod def query(request, basequery, sortsql="1 asc"): basesql, baseparams = basequery.query.get_compiler( basequery.db).as_sql(with_col_aliases=False) # Build the query query = """ select operation.name, location.name, operation.item_id, operation.description, operation.category, operation.subcategory, operation.type, operation.duration, operation.duration_per, operation.fence, operation.posttime, operation.sizeminimum, operation.sizemultiple, operation.sizemaximum, operation.priority, operation.effective_start, operation.effective_end, operation.cost, operation.search, operation.source, operation.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.lastmodified, item.description, item.category, item.subcategory, item.owner_id, item.source, item.lastmodified, res.bucket, res.startdate, res.enddate, res.proposed_start, res.total_start, res.proposed_end, res.total_end, res.proposed_production, res.total_production from operation left outer join item on operation.item_id = item.name left outer join location on operation.location_id = location.name inner join ( select oper.name as operation_id, d.bucket, d.startdate, d.enddate, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) proposed_start, coalesce(sum( case when d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) total_start, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate < operationplan.enddate and d.enddate >= operationplan.enddate then operationplan.quantity else 0 end ), 0) proposed_end, coalesce(sum( case when d.startdate < operationplan.enddate and d.enddate >= operationplan.enddate then operationplan.quantity else 0 end ), 0) total_end, coalesce(sum( case when operationplan.status = 'proposed' then ( -- Total overlap extract (epoch from least(operationplan.enddate, d.enddate) - greatest(operationplan.startdate, d.startdate)) -- Minus the interruptions - coalesce(( select sum(greatest(0, extract (epoch from least(to_timestamp(value->>1, 'YYYY-MM-DD HH24:MI:SS'), d.enddate) - greatest(to_timestamp(value->>0, 'YYYY-MM-DD HH24:MI:SS'), d.startdate) ))) from ( select * from jsonb_array_elements(plan->'interruptions')) breaks ), 0) ) / greatest(1, extract(epoch from operationplan.enddate - operationplan.startdate) - coalesce((plan#>>'{unavailable}')::numeric, 0)) * operationplan.quantity else 0 end ), 0) proposed_production, coalesce(sum( ( -- Total overlap extract (epoch from least(operationplan.enddate, d.enddate) - greatest(operationplan.startdate, d.startdate)) -- Minus the interruptions - coalesce(( select sum(greatest(0, extract (epoch from least(to_timestamp(value->>1, 'YYYY-MM-DD HH24:MI:SS'), d.enddate) - greatest(to_timestamp(value->>0, 'YYYY-MM-DD HH24:MI:SS'), d.startdate) ))) from ( select * from jsonb_array_elements(plan->'interruptions')) breaks ), 0) ) / greatest(1, extract(epoch from operationplan.enddate - operationplan.startdate) - coalesce((plan#>>'{unavailable}')::numeric, 0)) * operationplan.quantity ), 0) total_production from (%s) oper -- 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 -- Match overlapping operationplans left outer join operationplan on operationplan.operation_id = oper.name and (operationplan.startdate, operationplan.enddate) overlaps (d.startdate, d.enddate) group by oper.name, d.bucket, d.startdate, d.enddate ) res on res.operation_id = operation.name order by %s, res.startdate """ % ( basesql, request.report_bucket, request.report_startdate, request.report_enddate, sortsql, ) # Convert the SQl results to Python with transaction.atomic(using=request.database): with connections[ request.database].chunked_cursor() as cursor_chunked: cursor_chunked.execute(query, baseparams) for row in cursor_chunked: yield { "operation": row[0], "location": row[1], "item": row[2], "description": row[3], "category": row[4], "subcategory": row[5], "type": row[6], "duration": row[7], "duration_per": row[8], "fence": row[9], "posttime": row[10], "sizeminimum": row[11], "sizemultiple": row[12], "sizemaximum": row[13], "priority": row[14], "effective_start": row[15], "effective_end": row[16], "cost": row[17], "search": row[18], "source": row[19], "lastmodified": row[20], "location__description": row[21], "location__category": row[22], "location__subcategory": row[23], "location__available": row[24], "location__lastmodified": row[25], "item__description": row[26], "item__category": row[27], "item__subcategory": row[28], "item__owner": row[29], "item__source": row[30], "item__lastmodified": row[31], "bucket": row[32], "startdate": row[33].date(), "enddate": row[34].date(), "proposed_start": row[35], "total_start": row[36], "proposed_end": row[37], "total_end": row[38], "production_proposed": row[39], "production_total": row[40], }
class OverviewReport(GridPivot): """ A report showing the inventory profile of buffers. """ template = "output/buffer.html" title = _("Inventory report") @classmethod def basequeryset(reportclass, request, *args, **kwargs): if len(args) and args[0]: return Buffer.objects.all() else: return (OperationPlanMaterial.objects.values( "item", "location").order_by( "item_id", "location_id").distinct().annotate( buffer=RawSQL("item_id || ' @ ' || location_id", ()))) model = OperationPlanMaterial default_sort = (1, "asc", 2, "asc") permissions = (("view_inventory_report", "Can view inventory report"), ) help_url = "user-guide/user-interface/plan-analysis/inventory-report.html" rows = ( GridFieldText("buffer", title=_("buffer"), editable=False, key=True, initially_hidden=True), GridFieldText( "item", title=_("item"), editable=False, field_name="item__name", formatter="detail", extra='"role":"input/item"', ), GridFieldText( "location", title=_("location"), editable=False, field_name="location__name", formatter="detail", extra='"role":"input/location"', ), # Optional fields referencing the item GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="origin__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="origin__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), ) crosses = ( ("startoh", { "title": _("start inventory") }), ("startohdoc", { "title": _("start inventory days of cover") }), ("safetystock", { "title": _("safety stock") }), ("consumed", { "title": _("total consumed") }), ("consumedMO", { "title": _("consumed by MO") }), ("consumedDO", { "title": _("consumed by DO") }), ("consumedSO", { "title": _("consumed by SO") }), ("produced", { "title": _("total produced") }), ("producedMO", { "title": _("produced by MO") }), ("producedDO", { "title": _("produced by DO") }), ("producedPO", { "title": _("produced by PO") }), ("endoh", { "title": _("end inventory") }), ("total_in_progress", { "title": _("total in progress") }), ("work_in_progress_mo", { "title": _("work in progress MO") }), ("on_order_po", { "title": _("on order PO") }), ("in_transit_do", { "title": _("in transit DO") }), ) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 reportclass.attr_sql = "" # Adding custom item attributes for f in getAttributeFields(Item, initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += "item.%s, " % f.name.split("__")[-1] # Adding custom location attributes for f in getAttributeFields(Location, related_name_prefix="location", initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += "location.%s, " % f.name.split( "__")[-1] @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session["lasttab"] = "plan" return { "title": force_text(Buffer._meta.verbose_name) + " " + args[0], "post_title": _("plan"), } else: return {} @classmethod 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) # Execute the actual query query = """ select item.name||' @ '||location.name, item.name item_id, location.name location_id, item.description, item.category, item.subcategory, item.cost, item.owner_id, item.source, item.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.owner_id, location.source, location.lastmodified, %s (select jsonb_build_object('onhand', onhand, 'flowdate', to_char(flowdate,'YYYY-MM-DD HH24:MI:SS'), 'periodofcover', periodofcover) from operationplanmaterial where item_id = item.name and location_id = location.name and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1) startoh, d.bucket, d.startdate, d.enddate, (select safetystock from ( select 1 as priority, coalesce((select value from calendarbucket where calendar_id = 'SS for '||item.name||' @ '||location.name and greatest(d.startdate,%%s) >= startdate and greatest(d.startdate,%%s) < enddate order by priority limit 1), (select defaultvalue from calendar where name = 'SS for '||item.name||' @ '||location.name)) as safetystock union all select 2 as priority, coalesce((select value from calendarbucket where calendar_id = (select minimum_calendar_id from buffer where item_id = item.name and location_id = location.name) and greatest(d.startdate,%%s) >= startdate and greatest(d.startdate,%%s) < enddate order by priority limit 1), (select defaultvalue from calendar where name = (select minimum_calendar_id from buffer where name = item.name||' @ '||location.name))) as safetystock union all select 3 as priority, minimum as safetystock from buffer where item_id = item.name and location_id = location.name ) t where t.safetystock is not null order by priority limit 1) safetystock, (select jsonb_build_object( 'work_in_progress_mo', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 and operationplan.type = 'MO' then opm.quantity else 0 end), 'on_order_po', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 and operationplan.type = 'PO' then opm.quantity else 0 end), 'in_transit_do', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 and operationplan.type = 'DO' then opm.quantity else 0 end), 'total_in_progress', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'consumed', sum(case when (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'consumedMO', sum(case when operationplan.type = 'MO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'consumedDO', sum(case when operationplan.type = 'DO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'consumedSO', sum(case when operationplan.type = 'DLVR' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'produced', sum(case when (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'producedMO', sum(case when operationplan.type = 'MO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'producedDO', sum(case when operationplan.type = 'DO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'producedPO', sum(case when operationplan.type = 'PO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end) ) from operationplanmaterial opm inner join operationplan on operationplan.reference = opm.operationplan_id and ((startdate < d.enddate and enddate >= d.enddate) or (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate)) where opm.item_id = item.name and opm.location_id = location.name) ongoing from (%s) opplanmat inner join item on item.name = opplanmat.item_id inner join location on location.name = opplanmat.location_id -- 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 group by item.name, location.name, item.description, item.category, item.subcategory, item.cost, item.owner_id, item.source, item.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.owner_id, location.source, location.lastmodified, d.bucket, d.startdate, d.enddate order by %s, d.startdate """ % ( reportclass.attr_sql, basesql, sortsql, ) # Build the python result with connections[request.database].chunked_cursor() as cursor_chunked: cursor_chunked.execute( query, ( request.report_startdate, # startoh request.report_startdate, request.report_startdate, request.report_startdate, request.report_startdate, # safetystock ) + (request.report_startdate, ) * 9 + baseparams # ongoing + ( # opplanmat request.report_bucket, request.report_startdate, request.report_enddate, ), # bucket d ) for row in cursor_chunked: numfields = len(row) res = { "buffer": row[0], "item": row[1], "location": row[2], "item__description": row[3], "item__category": row[4], "item__cost": row[6], "item__owner": row[7], "item__source": row[8], "item__lastmodified": row[9], "location__description": row[10], "location__category": row[11], "location__subcategory": row[12], "location__available_id": row[13], "location__owner_id": row[14], "location__source": row[15], "location__lastmodified": row[16], "startoh": row[numfields - 6]["onhand"] if row[numfields - 6] else 0, "startohdoc": max( 0, 0 if (row[numfields - 6]["onhand"] if row[numfields - 6] else 0) <= 0 else (999 if row[numfields - 6]["periodofcover"] == 86313600 else (datetime.strptime(row[numfields - 6]["flowdate"], "%Y-%m-%d %H:%M:%S") + timedelta(seconds=row[numfields - 6]["periodofcover"]) - row[numfields - 4]).days if row[numfields - 6]["periodofcover"] else 999), ), "bucket": row[numfields - 5], "startdate": row[numfields - 4].date(), "enddate": row[numfields - 3].date(), "safetystock": row[numfields - 2] or 0, "consumed": row[numfields - 1]["consumed"] or 0, "consumedMO": row[numfields - 1]["consumedMO"] or 0, "consumedDO": row[numfields - 1]["consumedDO"] or 0, "consumedSO": row[numfields - 1]["consumedSO"] or 0, "produced": row[numfields - 1]["produced"] or 0, "producedMO": row[numfields - 1]["producedMO"] or 0, "producedDO": row[numfields - 1]["producedDO"] or 0, "producedPO": row[numfields - 1]["producedPO"] or 0, "total_in_progress": row[numfields - 1]["total_in_progress"] or 0, "work_in_progress_mo": row[numfields - 1]["work_in_progress_mo"] or 0, "on_order_po": row[numfields - 1]["on_order_po"] or 0, "in_transit_do": row[numfields - 1]["in_transit_do"] or 0, "endoh": float(row[numfields - 6]["onhand"] if row[numfields - 6] else 0) + float(row[numfields - 1]["produced"] or 0) - float(row[numfields - 1]["consumed"] or 0), } # Add attribute fields idx = 16 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
class DistributionReport(GridPivot): """ A report summarizing all distribution orders. """ template = "output/distribution_order_summary.html" title = _("Distribution order summary") model = DistributionOrder permissions = (("view_distributionorder", "Can view distribution order"), ) help_url = "user-interface/plan-analysis/distribution-order-summary.html" rows = ( GridFieldText( "key", key=True, search=False, initially_hidden=True, hidden=True, field_name="item__name", editable=False, ), GridFieldText( "item", title=_("item"), editable=False, field_name="item__name", formatter="detail", extra='"role":"input/item"', ), GridFieldText( "item__description", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("description")), ), GridFieldText( "item__category", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("category")), ), GridFieldText( "item__subcategory", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("subcategory")), ), GridFieldCurrency( "item__cost", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("cost")), field_name="item__cost", ), GridFieldText( "item__owner", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", ), GridFieldText( "item__source", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("source")), ), GridFieldLastModified( "item__lastmodified", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("last modified")), ), GridFieldText( "origin", title=_("origin"), editable=False, field_name="origin__name", formatter="detail", extra='"role":"input/location"', ), GridFieldText( "origin__description", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("origin"), _("description")), ), GridFieldText( "origin__category", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("origin"), _("category")), ), GridFieldText( "origin__subcategory", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("origin"), _("subcategory")), ), GridFieldText( "origin__available", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("origin"), _("available")), field_name="origin__available__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldLastModified( "origin__lastmodified", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("origin"), _("last modified")), ), GridFieldText( "destination", title=_("destination"), editable=False, field_name="destination__name", formatter="detail", extra='"role":"input/location"', ), GridFieldText( "destination__description", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("destination"), _("description")), ), GridFieldText( "destination__category", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("destination"), _("category")), ), GridFieldText( "destination__subcategory", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("destination"), _("subcategory")), ), GridFieldText( "destination__available", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("destination"), _("available")), field_name="destination__available__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldLastModified( "destination__lastmodified", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("destination"), _("last modified")), ), ) crosses = ( ("proposed_start", { "title": _("proposed shipping"), "initially_hidden": True }), ("total_start", { "title": _("total shipping") }), ("proposed_end", { "title": _("proposed receiving"), "initially_hidden": True }), ("total_end", { "title": _("total receiving") }), ( "proposed_in_transit", { "title": _("proposed in transit"), "initially_hidden": True }, ), ("total_in_transit", { "title": _("total in transit") }), ) @staticmethod def basequeryset(request, *args, **kwargs): current, start, end = getHorizon(request) return (DistributionOrder.objects.all( ).filter(startdate__lte=end, enddate__gte=start).annotate(key=RawSQL( "coalesce(item_id,'') || coalesce(origin_id,'') || coalesce(destination_id,'')", (), )).distinct("key").order_by() ) # Ordering isn't compatible with the distinct @classmethod def _apply_sort(reportclass, request, query): """ Applies a sort to the query. """ sortname = None if request.GET.get("sidx", ""): # 1) Sorting order specified on the request sortname = "%s %s" % (request.GET["sidx"], request.GET.get("sord", "asc")) elif request.prefs: # 2) Sorting order from the preferences sortname = "%s %s" % ( request.prefs.get("sidx", ""), request.GET.get("sord", "asc"), ) if not sortname or sortname == " asc": # 3) Default sort order return query.order_by("key") else: # Validate the field does exist. # We only validate the first level field, and not the fields # on related models. sortargs = [] for s in sortname.split(","): stripped = s.strip() if not stripped: continue sortfield, direction = stripped.split(" ", 1) try: query.order_by(sortfield).query.__str__() if direction.strip() != "desc": sortargs.append(sortfield) else: sortargs.append("-%s" % sortfield) except Exception: for r in reportclass.rows: if r.name == sortfield: try: query.order_by(r.field_name).query.__str__() if direction.strip() != "desc": sortargs.append(r.field_name) else: sortargs.append("-%s" % r.field_name) except Exception: # Can't sort on this field pass break if sortargs: return query.order_by( "key", *sortargs ) # The extra ordering by the 'key' is only change with the default method else: return query.order_by( "key" ) # The extra ordering by the 'key' is only change with the default method @staticmethod def query(request, basequery, sortsql="1 asc"): basesql, baseparams = basequery.query.get_compiler( basequery.db).as_sql(with_col_aliases=False) # Build the query query = """ with combinations as (%s) select row_to_json(data) from ( select -- Key field combinations.key as key, -- Attribute fields of item, location and supplier combinations.item_id as item, item.description as item__description, item.category as item__category, item.subcategory as item__subcategory, item.cost as item__cost, item.owner_id as item__owner, item.source as item__source, item.lastmodified as item__lastmodified, combinations.origin_id as origin, origin.description as origin__description, origin.category as origin__category, origin.subcategory as origin__subcategory, origin.available_id as origin__available, origin.lastmodified as origin__lastmodified, combinations.destination_id as destination, destination.description as destination__description, destination.category as destination__category, destination.subcategory as destination__subcategory, destination.available_id as destination__available, destination.lastmodified as destination__lastmodified, -- Buckets res.bucket as bucket, to_char(res.startdate, 'YYYY-MM-DD') as startdate, to_char(res.enddate, 'YYYY-MM-DD') as enddate, -- Values res.proposed_start as proposed_start, res.total_start as total_start, res.proposed_end as proposed_end, res.total_end as total_end, res.proposed_in_transit as proposed_in_transit, res.total_in_transit as total_in_transit from combinations inner join item on combinations.item_id = item.name left outer join location origin on combinations.origin_id = origin.name left outer join location destination on combinations.destination_id = destination.name inner join ( select operationplan.item_id, operationplan.origin_id, operationplan.destination_id, d.bucket, d.startdate, d.enddate, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) proposed_start, coalesce(sum( case when d.startdate <= operationplan.startdate and d.enddate > operationplan.startdate then operationplan.quantity else 0 end ), 0) total_start, coalesce(sum( case when operationplan.status = 'proposed' and d.startdate <= operationplan.enddate and d.enddate > operationplan.enddate then operationplan.quantity else 0 end ), 0) proposed_end, coalesce(sum( case when d.startdate <= operationplan.enddate and d.enddate > operationplan.enddate then operationplan.quantity else 0 end ), 0) total_end, coalesce(sum( case when operationplan.status = 'proposed' and d.enddate > operationplan.startdate and d.enddate <= operationplan.enddate then operationplan.quantity else 0 end ), 0) proposed_in_transit, coalesce(sum( case when d.enddate > operationplan.startdate and d.enddate <= operationplan.enddate then operationplan.quantity else 0 end ), 0) total_in_transit from operationplan inner join combinations on operationplan.item_id = combinations.item_id and operationplan.origin_id = combinations.origin_id and operationplan.destination_id = combinations.destination_id cross join ( select name as bucket, startdate, enddate from common_bucketdetail where bucket_id = '%s' and enddate > '%s' and startdate < '%s' ) d where operationplan.type = 'DO' group by operationplan.item_id, operationplan.origin_id, operationplan.destination_id, d.bucket, d.startdate, d.enddate ) res on res.item_id = combinations.item_id and res.origin_id = combinations.origin_id and res.destination_id = combinations.destination_id order by %s, res.startdate ) data """ % ( basesql, request.report_bucket, request.report_startdate, request.report_enddate, sortsql, ) # Convert the SQL results to Python with transaction.atomic(using=request.database): with connections[ request.database].chunked_cursor() as cursor_chunked: cursor_chunked.execute(query, baseparams) for row in cursor_chunked: yield row[0]
class OverviewReport(GridPivot): """ A report showing the independent demand for each item. """ template = "output/demand.html" title = _("Demand report") post_title = _("plan") basequeryset = Item.objects.all() model = Item permissions = (("view_demand_report", "Can view demand report"), ) rows = ( GridFieldText( "item", title=_("item"), key=True, editable=False, field_name="name", formatter="detail", extra='"role":"input/item"', ), GridFieldText("description", title=_("description"), initially_hidden=True), GridFieldText("category", title=_("category"), initially_hidden=True), GridFieldText("subcategory", title=_("subcategory"), initially_hidden=True), GridFieldText( "owner", title=_("owner"), field_name="owner__name", formatter="detail", extra='"role":"input/item"', initially_hidden=True, ), GridFieldCurrency("cost", title=_("cost"), initially_hidden=True), GridFieldText("source", title=_("source"), initially_hidden=True), GridFieldLastModified("lastmodified", initially_hidden=True), ) crosses = ( ("demand", { "title": _("sales orders") }), ("supply", { "title": _("supply") }), ("backlog", { "title": _("backlog") }), ("reasons", { "title": _("reasons"), "visible": False }), ) help_url = "user-interface/plan-analysis/demand-report.html" @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 reportclass.attr_sql = "" # Adding custom item attributes for f in getAttributeFields(Item, initially_hidden=False): f.editable = False reportclass.rows += (f, ) reportclass.attr_sql += "parent.%s, " % f.name.split("__")[-1] @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session["lasttab"] = "plan" return { "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": _("plan"), } else: return {} @classmethod 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]] = 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 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 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.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 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], "source": row[6], "lastmodified": row[7], "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": backlog, } idx = 8 for f in getAttributeFields(Item): res[f.field_name] = row[idx] idx += 1 yield res
class ResourceDetail(OperationPlanMixin, GridReport): template = "input/operationplanreport.html" title = _("resource detail") model = OperationPlanResource permissions = (("view_resource_report", "Can view resource report"), ) frozenColumns = 3 editable = True multiselect = True height = 250 help_url = "user-interface/plan-analysis/resource-detail-report.html" message_when_empty = Template(""" <h3>Resource detail</h3> <br> This table has a list of all manufacturing orders assigned to a certain certain resource.<br><br> The planning algorithm will populate this table, and as a user you normally don't need to create records in this table.<br> <br> """) @classmethod def basequeryset(reportclass, request, *args, **kwargs): if args and args[0]: try: res = Resource.objects.using( request.database).get(name__exact=args[0]) base = OperationPlanResource.objects.filter( resource__lft__gte=res.lft, resource__rght__lte=res.rght) except OperationPlanResource.DoesNotExist: base = OperationPlanResource.objects.filter( resource__exact=args[0]) else: base = OperationPlanResource.objects base = reportclass.operationplanExtraBasequery(base, request) if "calendarstart" in request.GET: base = base.filter( Q(operationplan__enddate__gte=request.GET["calendarstart"]) | (Q(operationplan__enddate__isnull=True) & Q(operationplan__startdate__gte=request. GET["calendarstart"]))) if "calendarend" in request.GET: base = base.filter( Q(operationplan__startdate__lte=request.GET["calendarend"]) | (Q(operationplan__startdate__isnull=True) & Q(operationplan__enddate__lte=request.GET["calendarend"]))) return base.select_related().annotate( opplan_duration=RawSQL( "(operationplan.enddate - operationplan.startdate)", []), opplan_net_duration=RawSQL( "(operationplan.enddate - operationplan.startdate - coalesce((operationplan.plan->>'unavailable')::int * interval '1 second', interval '0 second'))", [], ), setup_end=RawSQL("(operationplan.plan->>'setupend')", []), setup_duration=RawSQL("(operationplan.plan->>'setup')", []), feasible=RawSQL( "coalesce((operationplan.plan->>'feasible')::boolean, true)", []), ) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 # Adding custom operation attributes for f in getAttributeFields( Operation, related_name_prefix="operationplan__operation"): f.editable = False reportclass.rows += (f, ) # Adding custom resource attributes for f in getAttributeFields(Resource, related_name_prefix="resource"): f.editable = False reportclass.rows += (f, ) @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session["lasttab"] = "plandetail" return { "default_operationplan_type": "MO", "groupBy": "operationplan__status", "active_tab": "plandetail", "model": Resource, "title": force_text(Resource._meta.verbose_name) + " " + args[0], "post_title": _("plan detail"), } else: return { "default_operationplan_type": "MO", "groupBy": "operationplan__status", "active_tab": "plandetail", "model": OperationPlanResource, } rows = ( GridFieldInteger( "id", title="identifier", key=True, editable=False, formatter="detail", extra='"role":"input/operationplanresource"', initially_hidden=True, ), GridFieldText( "resource", title=_("resource"), field_name="resource__name", formatter="detail", extra='"role":"input/resource"', ), GridFieldText("operationplan__reference", title=_("reference"), editable=False), GridFieldText( "owner", title=_("owner"), field_name="operationplan__owner__reference", formatter="detail", extra="role:'input/manufacturingorder'", initially_hidden=True, ), GridFieldText( "color", title=_("inventory status"), formatter="color", field_name="operationplan__color", width="125", editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldText( "operationplan__item", title=_("item"), editable=False, formatter="detail", extra='"role":"input/item"', ), GridFieldText( "operationplan__location", title=_("location"), editable=False, formatter="detail", extra='"role":"input/location"', ), GridFieldText( "operationplan__operation__name", title=_("operation"), editable=False, formatter="detail", extra='"role":"input/operation"', ), GridFieldText( "operationplan__batch", title=_("batch"), editable=False, field_name="operationplan__batch", initially_hidden=True, ), GridFieldText( "operationplan__operation__description", title=format_lazy("{} - {}", _("operation"), _("description")), editable=False, initially_hidden=True, ), GridFieldText( "operationplan__operation__category", title=format_lazy("{} - {}", _("operation"), _("category")), editable=False, initially_hidden=True, ), GridFieldText( "operationplan__operation__subcategory", title=format_lazy("{} - {}", _("operation"), _("subcategory")), editable=False, initially_hidden=True, ), GridFieldText( "operationplan__operation__type", title=format_lazy("{} - {}", _("operation"), _("type")), initially_hidden=True, ), GridFieldDuration( "operationplan__operation__duration", title=format_lazy("{} - {}", _("operation"), _("duration")), initially_hidden=True, ), GridFieldDuration( "operationplan__operation__duration_per", title=format_lazy("{} - {}", _("operation"), _("duration per unit")), initially_hidden=True, ), GridFieldDuration( "operationplan__operation__fence", title=format_lazy("{} - {}", _("operation"), _("release fence")), initially_hidden=True, ), GridFieldDuration( "operationplan__operation__posttime", title=format_lazy("{} - {}", _("operation"), _("post-op time")), initially_hidden=True, ), GridFieldNumber( "operationplan__operation__sizeminimum", title=format_lazy("{} - {}", _("operation"), _("size minimum")), initially_hidden=True, ), GridFieldNumber( "operationplan__operation__sizemultiple", title=format_lazy("{} - {}", _("operation"), _("size multiple")), initially_hidden=True, ), GridFieldNumber( "operationplan__operation__sizemaximum", title=format_lazy("{} - {}", _("operation"), _("size maximum")), initially_hidden=True, ), GridFieldInteger( "operationplan__operation__priority", title=format_lazy("{} - {}", _("operation"), _("priority")), initially_hidden=True, ), GridFieldDateTime( "operationplan__operation__effective_start", title=format_lazy("{} - {}", _("operation"), _("effective start")), initially_hidden=True, ), GridFieldDateTime( "operationplan__operation__effective_end", title=format_lazy("{} - {}", _("operation"), _("effective end")), initially_hidden=True, ), GridFieldCurrency( "operationplan__operation__cost", title=format_lazy("{} - {}", _("operation"), _("cost")), initially_hidden=True, ), GridFieldText( "operationplan__operation__search", title=format_lazy("{} - {}", _("operation"), _("search mode")), initially_hidden=True, ), GridFieldText( "operationplan__operation__source", title=format_lazy("{} - {}", _("operation"), _("source")), initially_hidden=True, ), GridFieldLastModified( "operationplan__operation__lastmodified", title=format_lazy("{} - {}", _("operation"), _("last modified")), initially_hidden=True, ), GridFieldDateTime( "operationplan__startdate", title=_("start date"), editable=False, extra= '"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"min"', ), GridFieldDateTime( "operationplan__enddate", title=_("end date"), editable=False, extra= '"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"max"', ), GridFieldDuration( "opplan_duration", title=_("duration"), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldDuration( "opplan_net_duration", title=_("net duration"), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldNumber( "operationplan__quantity", title=_("quantity"), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldText("operationplan__status", title=_("status"), editable=False), GridFieldNumber( "operationplan__criticality", title=_("criticality"), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldDuration( "delay", title=_("delay"), field_name="operationplan__delay", editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"', ), GridFieldText( "demands", title=_("demands"), formatter="demanddetail", extra='"role":"input/demand"', width=300, editable=False, sortable=False, ), GridFieldText( "operationplan__type", title=_("type"), field_name="operationplan__type", editable=False, ), GridFieldNumber( "quantity", title=_("load quantity"), extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldText("setup", title=_("setup"), editable=False, initially_hidden=True), GridFieldDateTime( "setup_end", title=_("setup end date"), editable=False, initially_hidden=True, ), GridFieldDuration( "setup_duration", title=_("setup duration"), editable=False, initially_hidden=True, search=False, ), GridFieldBool( "feasible", title=_("feasible"), editable=False, initially_hidden=True, search=False, ), # Optional fields referencing the item GridFieldText( "operationplan__item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "operationplan__item__description", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("description")), ), GridFieldText( "operationplan__item__category", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("category")), ), GridFieldText( "operationplan__item__subcategory", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("subcategory")), ), GridFieldCurrency( "operationplan__item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldNumber( "operationplan__item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, ), GridFieldNumber( "operationplan__item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, ), GridFieldText( "operationplan__item__owner", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("owner")), field_name="operationplan__item__owner__name", ), GridFieldText( "operationplan__item__source", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("source")), ), GridFieldLastModified( "operationplan__item__lastmodified", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("item"), _("last modified")), ), # Optional fields referencing the operation location GridFieldText( "operationplan__operation__location__description", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("location"), _("description")), ), GridFieldText( "operationplan__operation__location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "operationplan__operation__location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "operationplan__operation__location__available", editable=False, title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="operationplan__operation__location__available__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldText( "operationplan__operation__location__owner", initially_hidden=True, title=format_lazy("{} - {}", _("location"), _("owner")), field_name="operationplan__operation__location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "operationplan__operation__location__source", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("location"), _("source")), ), GridFieldLastModified( "operationplan__operation__location__lastmodified", initially_hidden=True, editable=False, title=format_lazy("{} - {}", _("location"), _("last modified")), ), # Optional fields referencing the resource GridFieldText( "resource__description", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("description")), ), GridFieldText( "resource__category", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("category")), ), GridFieldText( "resource__subcategory", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("subcategory")), ), GridFieldText( "resource__type", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("type")), ), GridFieldBool( "resource__constrained", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("constrained")), ), GridFieldNumber( "resource__maximum", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("maximum")), ), GridFieldText( "resource__maximum_calendar", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("maximum calendar")), field_name="resource__maximum_calendar__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldCurrency( "resource__cost", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("cost")), ), GridFieldDuration( "resource__maxearly", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("maxearly")), ), GridFieldText( "resource__setupmatrix", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("setupmatrix")), field_name="resource__setupmatrix__name", formatter="detail", extra='"role":"input/setupmatrix"', ), GridFieldText( "resource__setup", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("setup")), ), GridFieldText( "resource_location", editable=False, initially_hidden=True, title=format_lazy("{} - {}", _("resource"), _("location")), field_name="resource__location__name", formatter="detail", extra='"role":"input/location"', ), # Optional fields referencing the resource location GridFieldText( "resource__location__description", initially_hidden=True, editable=False, title=format_lazy("{} - {} - {}", _("resource"), _("location"), _("description")), ), GridFieldText( "resource__location__category", initially_hidden=True, editable=False, title=format_lazy("{} - {} - {}", _("resource"), _("location"), _("category")), ), GridFieldText( "resource__location__subcategory", initially_hidden=True, editable=False, title=format_lazy("{} - {} - {}", _("resource"), _("location"), _("subcategory")), ), GridFieldText( "resource__location__available", initially_hidden=True, editable=False, title=format_lazy("{} - {} - {}", _("resource"), _("location"), _("available")), field_name="resource__location__available__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldText( "resource__location__owner", extra='"role":"input/location"', editable=False, title=format_lazy("{} - {} - {}", _("resource"), _("location"), _("owner")), initially_hidden=True, field_name="resource__location__owner__name", formatter="detail", ), GridFieldText( "resource__location__source", initially_hidden=True, editable=False, title=format_lazy("{} - {} - {}", _("resource"), _("location"), _("source")), ), # Status field currently not used # GridFieldChoice('status', title=_('load status'), choices=OperationPlanResource.OPRstatus), GridFieldLastModified("lastmodified", initially_hidden=True), )
class OverviewReport(GridPivot): ''' A report showing the inventory profile of buffers. ''' template = 'output/buffer.html' title = _('Inventory report') @classmethod def basequeryset(reportclass, request, *args, **kwargs): if len(args) and args[0]: return Buffer.objects.all() else: return OperationPlanMaterial.objects.values('item', 'location') \ .order_by('item_id', 'location_id') \ .distinct() \ .annotate( buffer=RawSQL("item_id || ' @ ' || location_id", ()) ) model = OperationPlanMaterial default_sort = (1, 'asc', 2, 'asc') permissions = (('view_inventory_report', 'Can view inventory report'), ) help_url = 'user-guide/user-interface/plan-analysis/inventory-report.html' rows = ( GridFieldText('buffer', title=_('buffer'), editable=False, key=True, initially_hidden=True), GridFieldText('item', title=_('item'), editable=False, field_name='item__name', formatter='detail', extra='"role":"input/item"'), GridFieldText('location', title=_('location'), editable=False, field_name='location__name', formatter='detail', extra='"role":"input/location"'), # Optional fields referencing the item GridFieldText('item__description', title=string_concat(_('item'), ' - ', _('description')), initially_hidden=True, editable=False), GridFieldText('item__category', title=string_concat(_('item'), ' - ', _('category')), initially_hidden=True, editable=False), GridFieldText('item__subcategory', title=string_concat(_('item'), ' - ', _('subcategory')), initially_hidden=True, editable=False), GridFieldNumber('item__cost', title=string_concat(_('item'), ' - ', _('cost')), initially_hidden=True, editable=False), GridFieldText('item__owner', title=string_concat(_('item'), ' - ', _('owner')), field_name='item__owner__name', initially_hidden=True, editable=False), GridFieldText('item__source', title=string_concat(_('item'), ' - ', _('source')), initially_hidden=True, editable=False), GridFieldLastModified('item__lastmodified', title=string_concat(_('item'), ' - ', _('last modified')), initially_hidden=True, editable=False), # Optional fields referencing the location GridFieldText('location__description', title=string_concat(_('location'), ' - ', _('description')), initially_hidden=True, editable=False), GridFieldText('location__category', title=string_concat(_('location'), ' - ', _('category')), initially_hidden=True, editable=False), GridFieldText('location__subcategory', title=string_concat(_('location'), ' - ', _('subcategory')), initially_hidden=True, editable=False), GridFieldText('location__available', title=string_concat(_('location'), ' - ', _('available')), initially_hidden=True, field_name='origin__available__name', formatter='detail', extra='"role":"input/calendar"', editable=False), GridFieldText('location__owner', title=string_concat(_('location'), ' - ', _('owner')), initially_hidden=True, field_name='origin__owner__name', formatter='detail', extra='"role":"input/location"', editable=False), GridFieldText('location__source', title=string_concat(_('location'), ' - ', _('source')), initially_hidden=True, editable=False), GridFieldLastModified('location__lastmodified', title=string_concat(_('location'), ' - ', _('last modified')), initially_hidden=True, editable=False), ) crosses = ( ('startoh', { 'title': _('start inventory') }), ('startohdoc', { 'title': _('start inventory days of cover') }), ('safetystock', { 'title': _('safety stock') }), ('consumed', { 'title': _('total consumed') }), ('consumedMO', { 'title': _('consumed by MO') }), ('consumedDO', { 'title': _('consumed by DO') }), ('consumedSO', { 'title': _('consumed by SO') }), ('produced', { 'title': _('total produced') }), ('producedMO', { 'title': _('produced by MO') }), ('producedDO', { 'title': _('produced by DO') }), ('producedPO', { 'title': _('produced by PO') }), ('endoh', { 'title': _('end inventory') }), ('total_in_progress', { 'title': _('total in progress') }), ('work_in_progress_mo', { 'title': _('work in progress MO') }), ('on_order_po', { 'title': _('on order PO') }), ('in_transit_do', { 'title': _('in transit DO') }), ) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 reportclass.attr_sql = '' # Adding custom item attributes for f in getAttributeFields(Item, initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += 'item.%s, ' % f.name.split('__')[-1] # Adding custom location attributes for f in getAttributeFields(Location, related_name_prefix="location", initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += 'location.%s, ' % f.name.split( '__')[-1] @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session['lasttab'] = 'plan' return { 'title': force_text(Buffer._meta.verbose_name) + " " + args[0], 'post_title': _('plan') } else: return {} @classmethod 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) # Execute the actual query query = ''' select item.name||' @ '||location.name, item.name item_id, location.name location_id, item.description, item.category, item.subcategory, item.cost, item.owner_id, item.source, item.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.owner_id, location.source, location.lastmodified, %s (select jsonb_build_object('onhand', onhand, 'flowdate', to_char(flowdate,'YYYY-MM-DD HH24:MI:SS'), 'periodofcover', periodofcover) from operationplanmaterial where item_id = item.name and location_id = location.name and flowdate < greatest(d.startdate,%%s) order by flowdate desc, id desc limit 1) startoh, d.bucket, d.startdate, d.enddate, (select safetystock from ( select 1 as priority, coalesce((select value from calendarbucket where calendar_id = 'SS for '||item.name||' @ '||location.name and greatest(d.startdate,%%s) >= startdate and greatest(d.startdate,%%s) < enddate order by priority limit 1), (select defaultvalue from calendar where name = 'SS for '||item.name||' @ '||location.name)) as safetystock union all select 2 as priority, coalesce((select value from calendarbucket where calendar_id = (select minimum_calendar_id from buffer where name = item.name||' @ '||location.name) and greatest(d.startdate,%%s) >= startdate and greatest(d.startdate,%%s) < enddate order by priority limit 1), (select defaultvalue from calendar where name = (select minimum_calendar_id from buffer where name = item.name||' @ '||location.name))) as safetystock union all select 3 as priority, minimum as safetystock from buffer where name = item.name||' @ '||location.name ) t where t.safetystock is not null order by priority limit 1) safetystock, (select jsonb_build_object( 'work_in_progress_mo', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 and operationplan.type = 'MO' then opm.quantity else 0 end), 'on_order_po', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 and operationplan.type = 'PO' then opm.quantity else 0 end), 'in_transit_do', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 and operationplan.type = 'DO' then opm.quantity else 0 end), 'total_in_progress', sum(case when (startdate < d.enddate and enddate >= d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'consumed', sum(case when (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'consumedMO', sum(case when operationplan.type = 'MO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'consumedDO', sum(case when operationplan.type = 'DO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'consumedSO', sum(case when operationplan.type = 'DLVR' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity < 0 then -opm.quantity else 0 end), 'produced', sum(case when (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'producedMO', sum(case when operationplan.type = 'MO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'producedDO', sum(case when operationplan.type = 'DO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end), 'producedPO', sum(case when operationplan.type = 'PO' and (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate) and opm.quantity > 0 then opm.quantity else 0 end) ) from operationplanmaterial opm inner join operationplan on operationplan.reference = opm.operationplan_id and ((startdate < d.enddate and enddate >= d.enddate) or (opm.flowdate >= greatest(d.startdate,%%s) and opm.flowdate < d.enddate)) where opm.item_id = item.name and opm.location_id = location.name) ongoing from (%s) opplanmat inner join item on item.name = opplanmat.item_id inner join location on location.name = opplanmat.location_id -- 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 group by item.name, location.name, item.description, item.category, item.subcategory, item.cost, item.owner_id, item.source, item.lastmodified, location.description, location.category, location.subcategory, location.available_id, location.owner_id, location.source, location.lastmodified, d.bucket, d.startdate, d.enddate order by %s, d.startdate ''' % (reportclass.attr_sql, basesql, sortsql) # Build the python result with connections[request.database].chunked_cursor() as cursor_chunked: cursor_chunked.execute( query, ( request.report_startdate, # startoh request.report_startdate, request.report_startdate, request.report_startdate, request.report_startdate, # safetystock ) + (request.report_startdate, ) * 9 + # ongoing baseparams + # opplanmat (request.report_bucket, request.report_startdate, request.report_enddate), # bucket d ) for row in cursor_chunked: numfields = len(row) res = { 'buffer': row[0], 'item': row[1], 'location': row[2], 'item__description': row[3], 'item__category': row[4], 'item__cost': row[6], 'item__owner': row[7], 'item__source': row[8], 'item__lastmodified': row[9], 'location__description': row[10], 'location__category': row[11], 'location__subcategory': row[12], 'location__available_id': row[13], 'location__owner_id': row[14], 'location__source': row[15], 'location__lastmodified': row[16], 'startoh': row[numfields - 6]['onhand'] if row[numfields - 6] else 0, 'startohdoc': 0 if (row[numfields - 6]['onhand'] if row[numfields - 6] else 0) <= 0\ else (999 if row[numfields - 6]['periodofcover'] == 86313600\ else (datetime.strptime(row[numfields - 6]['flowdate'],'%Y-%m-%d %H:%M:%S') +\ timedelta(seconds=row[numfields - 6]['periodofcover']) - row[numfields - 4]).days if row[numfields - 6]['periodofcover'] else 999), 'bucket': row[numfields - 5], 'startdate': row[numfields - 4].date(), 'enddate': row[numfields - 3].date(), 'safetystock': row[numfields - 2] or 0, 'consumed': row[numfields - 1]['consumed'] or 0, 'consumedMO': row[numfields - 1]['consumedMO'] or 0, 'consumedDO': row[numfields - 1]['consumedDO'] or 0, 'consumedSO': row[numfields - 1]['consumedSO'] or 0, 'produced': row[numfields - 1]['produced'] or 0, 'producedMO': row[numfields - 1]['producedMO'] or 0, 'producedDO': row[numfields - 1]['producedDO'] or 0, 'producedPO': row[numfields - 1]['producedPO'] or 0, 'total_in_progress': row[numfields - 1]['total_in_progress'] or 0, 'work_in_progress_mo': row[numfields - 1]['work_in_progress_mo'] or 0, 'on_order_po': row[numfields - 1]['on_order_po'] or 0, 'in_transit_do': row[numfields - 1]['in_transit_do'] or 0, 'endoh': float(row[numfields - 6]['onhand'] if row[numfields - 6] else 0) + float(row[numfields - 1]['produced'] or 0) - float(row[numfields - 1]['consumed'] or 0), } # Add attribute fields idx = 16 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
class BufferList(GridReport): title = _("buffers") basequeryset = Buffer.objects.all() model = Buffer frozenColumns = 1 help_url = "modeling-wizard/master-data/buffers.html" rows = ( GridFieldInteger( "id", title=_("identifier"), key=True, formatter="detail", extra='"role":"input/buffer"', initially_hidden=True, ), GridFieldText("description", title=_("description")), GridFieldText("category", title=_("category"), initially_hidden=True), GridFieldText("subcategory", title=_("subcategory"), initially_hidden=True), GridFieldHierarchicalText( "location", title=_("location"), field_name="location__name", formatter="detail", extra='"role":"input/location"', model=Location, ), GridFieldText( "item", title=_("item"), field_name="item__name", formatter="detail", extra='"role":"input/item"', ), GridFieldText( "batch", title=_("batch"), field_name="batch", initially_hidden=True ), GridFieldNumber("onhand", title=_("onhand")), GridFieldChoice("type", title=_("type"), choices=Buffer.types), GridFieldNumber("minimum", title=_("minimum")), GridFieldText( "minimum_calendar", title=_("minimum calendar"), field_name="minimum_calendar__name", formatter="detail", extra='"role":"input/calendar"', initially_hidden=True, ), GridFieldText("source", title=_("source"), initially_hidden=True), GridFieldLastModified("lastmodified"), # Optional fields referencing the item GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldCurrency( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), )
class DetailReport(GridReport): ''' A list report to show OperationPlanMaterial. ''' template = 'input/operationplanreport.html' title = _("Inventory detail report") model = OperationPlanMaterial permissions = (('view_inventory_report', 'Can view inventory report'), ) frozenColumns = 0 editable = False multiselect = False height = 250 help_url = 'user-guide/user-interface/plan-analysis/inventory-detail-report.html' @classmethod def basequeryset(reportclass, request, args, kwargs): if len(args) and args[0]: dlmtr = args[0].find(" @ ") base = OperationPlanMaterial.objects.filter( item=args[0][:dlmtr], location=args[0][dlmtr + 3:]) else: base = OperationPlanMaterial.objects return base.select_related().extra( select={ 'pegging': "(select string_agg(value || ' : ' || key, ', ') from (select key, value from jsonb_each_text(plan->'pegging') order by key desc) peg)" }) @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session['lasttab'] = 'plandetail' return {'active_tab': 'plandetail', 'model': Buffer} else: return {'active_tab': 'plandetail', 'model': None} rows = ( #. Translators: Translation included with Django GridFieldInteger('id', title=_('internal id'), key=True, editable=False, hidden=True), GridFieldText('item', title=_('item'), field_name='item__name', editable=False, formatter='detail', extra='"role":"input/item"'), GridFieldText('location', title=_('location'), field_name='location__name', editable=False, formatter='detail', extra='"role":"input/location"'), GridFieldInteger('operationplan__id', title=_('identifier'), editable=False), GridFieldText('operationplan__reference', title=_('reference'), editable=False), GridFieldText( 'operationplan__color', title=_('inventory status'), formatter='color', width='125', editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"'), GridFieldText('operationplan__type', title=_('type'), field_name='operationplan__type', editable=False), GridFieldText('operationplan__name', title=_('operation'), editable=False, field_name='operationplan__name', formatter='detail', extra='"role":"input/operation"'), GridFieldText('operationplan__operation__description', title=string_concat(_('operation'), ' - ', _('description')), editable=False, initially_hidden=True), GridFieldText('operationplan__operation__category', title=string_concat(_('operation'), ' - ', _('category')), editable=False, initially_hidden=True), GridFieldText('operationplan__operation__subcategory', title=string_concat(_('operation'), ' - ', _('subcategory')), editable=False, initially_hidden=True), GridFieldText('operationplan__operation__type', title=string_concat(_('operation'), ' - ', _('type')), initially_hidden=True), GridFieldDuration('operationplan__operation__duration', title=string_concat(_('operation'), ' - ', _('duration')), initially_hidden=True), GridFieldDuration('operationplan__operation__duration_per', title=string_concat(_('operation'), ' - ', _('duration per unit')), initially_hidden=True), GridFieldDuration('operationplan__operation__fence', title=string_concat(_('operation'), ' - ', _('release fence')), initially_hidden=True), GridFieldDuration('operationplan__operation__posttime', title=string_concat(_('operation'), ' - ', _('post-op time')), initially_hidden=True), GridFieldNumber('operationplan__operation__sizeminimum', title=string_concat(_('operation'), ' - ', _('size minimum')), initially_hidden=True), GridFieldNumber('operationplan__operation__sizemultiple', title=string_concat(_('operation'), ' - ', _('size multiple')), initially_hidden=True), GridFieldNumber('operationplan__operation__sizemaximum', title=string_concat(_('operation'), ' - ', _('size maximum')), initially_hidden=True), GridFieldInteger('operationplan__operation__priority', title=string_concat(_('operation'), ' - ', _('priority')), initially_hidden=True), GridFieldDateTime('operationplan__operation__effective_start', title=string_concat(_('operation'), ' - ', _('effective start')), initially_hidden=True), GridFieldDateTime('operationplan__operation__effective_end', title=string_concat(_('operation'), ' - ', _('effective end')), initially_hidden=True), GridFieldCurrency('operationplan__operation__cost', title=string_concat(_('operation'), ' - ', _('cost')), initially_hidden=True), GridFieldText('operationplan__operation__search', title=string_concat(_('operation'), ' - ', _('search mode')), initially_hidden=True), GridFieldText('operationplan__operation__source', title=string_concat(_('operation'), ' - ', _('source')), initially_hidden=True), GridFieldLastModified('operationplan__operation__lastmodified', title=string_concat(_('operation'), ' - ', _('last modified')), initially_hidden=True), GridFieldDateTime('flowdate', title=_('date'), editable=False), GridFieldNumber('quantity', title=_('quantity'), editable=False), GridFieldNumber('onhand', title=_('onhand'), editable=False), GridFieldText('operationplan__status', title=_('status'), editable=False, field_name='operationplan__status'), GridFieldNumber( 'operationplan__criticality', title=_('criticality'), field_name='operationplan__criticality', editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"'), GridFieldDuration( 'operationplan__delay', title=_('delay'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"'), GridFieldNumber( 'operationplan__quantity', title=_('operationplan quantity'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"'), GridFieldText('pegging', title=_('demands'), formatter='demanddetail', extra='"role":"input/demand"', width=300, editable=False, sortable=False), )
class DistributionOrderList(OperationPlanMixin, GridReport): template = "input/operationplanreport.html" title = _("distribution orders") default_sort = (1, "desc") model = DistributionOrder frozenColumns = 1 multiselect = True editable = True height = 250 help_url = "modeling-wizard/distribution/distribution-orders.html" @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: paths = request.path.split("/") if paths[4] == "operationplanmaterial": return { "active_tab": "distributionorders", "model": Item, "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": force_text( _("in transit in %(loc)s at %(date)s") % {"loc": args[1], "date": args[2]} ), } elif paths[4] == "produced": return { "active_tab": "distributionorders", "model": Item, "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": force_text( _("received in %(loc)s between %(date1)s and %(date2)s") % {"loc": args[1], "date1": args[2], "date2": args[3]} ), } elif paths[4] == "consumed": return { "active_tab": "distributionorders", "model": Item, "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": force_text( _("shipped from %(loc)s between %(date1)s and %(date2)s") % {"loc": args[1], "date1": args[2], "date2": args[3]} ), } elif paths[4] == "item": return { "active_tab": "distributionorders", "model": Item, "title": force_text(Item._meta.verbose_name) + " " + args[0], "post_title": _("distribution orders"), } elif paths[4] == "location": path = paths[-2] if path == "in": return { "active_tab": "inboundorders", "model": Location, "title": force_text(Location._meta.verbose_name) + " " + args[0], "post_title": _("inbound distribution"), } elif path == "out": return { "active_tab": "outboundorders", "model": Location, "title": force_text(Location._meta.verbose_name) + " " + args[0], "post_title": _("outbound distribution"), } else: return {"active_tab": "edit", "model": Item} else: return {"active_tab": "edit"} @classmethod def basequeryset(reportclass, request, *args, **kwargs): q = DistributionOrder.objects.all() if args and args[0]: paths = request.path.split("/") if paths[4] == "operationplanmaterial": q = q.filter(Q(origin=args[1]) | Q(destination=args[1])).filter( item__name=args[0], startdate__lt=args[2], enddate__gte=args[2] ) elif paths[4] == "item": q = q.filter(item__name=args[0]) elif paths[4] == "produced": q = q.filter( destination__name=args[1], item__name=args[0], enddate__gte=args[2], enddate__lt=args[3], ) elif paths[4] == "consumed": q = q.filter( origin__name=args[1], item__name=args[0], startdate__gte=args[2], startdate__lt=args[3], ) elif paths[4] == "location": path = paths[-2] if path == "out": q = q.filter(origin_id=args[0]) elif path == "in": q = q.filter(destination_id=args[0]) q = reportclass.operationplanExtraBasequery(q, request) return q.annotate( total_cost=Cast(F("item__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 """, [], ), ) rows = ( GridFieldText( "reference", title=_("reference"), key=True, formatter="detail", extra='role:"input/distributionorder"', editable=not settings.ERP_CONNECTOR, ), GridFieldNumber( "computed_color", title=_("inventory status"), formatter="color", width="125", editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldNumber("color", hidden=True), GridFieldHierarchicalText( "item", title=_("item"), field_name="item__name", formatter="detail", extra='"role":"input/item"', model=Item, ), GridFieldText( "origin", title=_("origin"), field_name="origin__name", formatter="detail", extra='"role":"input/location"', ), GridFieldText( "destination", title=_("destination"), field_name="destination__name", formatter="detail", extra='"role":"input/location"', ), GridFieldDateTime( "startdate", title=_("shipping date"), extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"min"', ), GridFieldDateTime( "enddate", title=_("receipt date"), extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"max"', ), GridFieldNumber( "quantity", title=_("quantity"), extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldChoice( "status", title=_("status"), choices=DistributionOrder.orderstatus, editable=not settings.ERP_CONNECTOR, ), GridFieldCurrency( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"', ), GridFieldNumber( "item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"', ), GridFieldNumber( "item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"', ), GridFieldCurrency( "total_cost", title=_("total cost"), editable=False, search=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldNumber( "total_volume", title=_("total volume"), editable=False, search=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldNumber( "total_weight", title=_("total weight"), editable=False, search=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"', ), GridFieldText( "batch", title=_("batch"), editable="true", initially_hidden=True ), GridFieldNumber( "criticality", title=_("criticality"), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"', ), GridFieldDuration( "delay", title=_("delay"), editable=False, initially_hidden=True, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"', ), GridFieldJSON( "demands", title=_("demands"), editable=False, search=True, sortable=False, formatter="demanddetail", extra='"role":"input/demand"', ), GridFieldText("source", title=_("source"), initially_hidden=True), GridFieldLastModified("lastmodified"), GridFieldBool( "feasible", title=_("feasible"), editable=False, initially_hidden=True, search=False, ), # Optional fields referencing the item GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the origin location GridFieldText( "origin__description", title=format_lazy("{} - {}", _("origin"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "origin__category", title=format_lazy("{} - {}", _("origin"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "origin__subcategory", title=format_lazy("{} - {}", _("origin"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "origin__available", title=format_lazy("{} - {}", _("origin"), _("available")), initially_hidden=True, field_name="origin__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "origin__owner", title=format_lazy("{} - {}", _("origin"), _("owner")), initially_hidden=True, field_name="origin__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "origin__source", title=format_lazy("{} - {}", _("origin"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "origin__lastmodified", title=format_lazy("{} - {}", _("origin"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the destination location GridFieldText( "destination__description", title=format_lazy("{} - {}", _("destination"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "destination__category", title=format_lazy("{} - {}", _("destination"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "destination__subcategory", title=format_lazy("{} - {}", _("destination"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "destination__available", title=format_lazy("{} - {}", _("destination"), _("available")), initially_hidden=True, field_name="origin__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "destination__owner", title=format_lazy("{} - {}", _("destination"), _("owner")), initially_hidden=True, field_name="origin__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "destination__source", title=format_lazy("{} - {}", _("destination"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "destination__lastmodified", title=format_lazy("{} - {}", _("destination"), _("last modified")), initially_hidden=True, editable=False, ), GridFieldText( "end_items", title=_("end items"), editable=False, search=False, sortable=False, initially_hidden=True, formatter="listdetail", extra='"role":"input/item"', ), ) if settings.ERP_CONNECTOR: actions = [ { "name": "erp_incr_export", "label": format_lazy("export to {erp}", erp=settings.ERP_CONNECTOR), "function": "ERPconnection.IncrementalExport(jQuery('#grid'),'DO')", } ] else: actions = [ { "name": "proposed", "label": format_lazy( _("change status to {status}"), status=_("proposed") ), "function": "grid.setStatus('proposed')", }, { "name": "approved", "label": format_lazy( _("change status to {status}"), status=_("approved") ), "function": "grid.setStatus('approved')", }, { "name": "confirmed", "label": format_lazy( _("change status to {status}"), status=_("confirmed") ), "function": "grid.setStatus('confirmed')", }, { "name": "completed", "label": format_lazy( _("change status to {status}"), status=_("completed") ), "function": "grid.setStatus('completed')", }, { "name": "closed", "label": format_lazy( _("change status to {status}"), status=_("closed") ), "function": "grid.setStatus('closed')", }, ] @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 for f in getAttributeFields(DistributionOrder): reportclass.rows += (f,) for f in getAttributeFields(Item, related_name_prefix="item"): f.editable = False reportclass.rows += (f,) for f in getAttributeFields(Location, related_name_prefix="origin"): f.editable = False reportclass.rows += (f,) for f in getAttributeFields(Location, related_name_prefix="destination"): f.editable = False reportclass.rows += (f,)
class ResourceList(GridReport): title = _("resources") basequeryset = Resource.objects.all() model = Resource frozenColumns = 1 help_url = "modeling-wizard/manufacturing-capacity/resources.html" rows = ( GridFieldText( "name", title=_("name"), key=True, formatter="detail", extra='"role":"input/resource"', ), GridFieldText("description", title=_("description")), GridFieldText("category", title=_("category"), initially_hidden=True), GridFieldText("subcategory", title=_("subcategory"), initially_hidden=True), GridFieldHierarchicalText( "location", title=_("location"), field_name="location__name", formatter="detail", extra='"role":"input/location"', model=Location, ), GridFieldText( "owner", title=_("owner"), field_name="owner__name", formatter="detail", extra='"role":"input/resource"', initially_hidden=True, ), GridFieldChoice("type", title=_("type"), choices=Resource.types), GridFieldBool("constrained", title=_("constrained")), GridFieldNumber("maximum", title=_("maximum")), GridFieldText( "maximum_calendar", title=_("maximum calendar"), field_name="maximum_calendar__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldText( "available", title=_("available"), field_name="available__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldCurrency("cost", title=_("cost"), initially_hidden=True), GridFieldDuration("maxearly", title=_("maxearly"), initially_hidden=True), GridFieldText( "setupmatrix", title=_("setup matrix"), field_name="setupmatrix__name", formatter="detail", extra='"role":"input/setupmatrix"', initially_hidden=True, ), GridFieldText("setup", title=_("setup"), initially_hidden=True), GridFieldText("source", title=_("source"), initially_hidden=True), # Translator: xgettext:no-python-format GridFieldNumber("efficiency", title=_("efficiency %"), formatter="percentage"), GridFieldText( "efficiency_calendar", # Translator: xgettext:no-python-format title=_("efficiency % calendar"), initially_hidden=True, field_name="efficiency_calendar__name", formatter="detail", extra='"role":"input/calendar"', ), GridFieldLastModified("lastmodified"), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), )
class DetailReport(OperationPlanMixin, GridReport): ''' A list report to show OperationPlanMaterial. ''' template = 'input/operationplanreport.html' title = _("Inventory detail report") model = OperationPlanMaterial permissions = (('view_inventory_report', 'Can view inventory report'),) frozenColumns = 0 editable = False multiselect = False height = 250 help_url = 'user-guide/user-interface/plan-analysis/inventory-detail-report.html' @ classmethod def basequeryset(reportclass, request, *args, **kwargs): if len(args) and args[0]: dlmtr = args[0].find(" @ ") base = OperationPlanMaterial.objects.filter( item=args[0][:dlmtr], location=args[0][dlmtr + 3:] ) else: base = OperationPlanMaterial.objects base = reportclass.operationplanExtraBasequery(base, request) return base.select_related().extra(select={ 'feasible': "coalesce((operationplan.plan->>'feasible')::boolean, true)", }) @classmethod def extra_context(reportclass, request, *args, **kwargs): if args and args[0]: request.session['lasttab'] = 'plandetail' return { 'active_tab': 'plandetail', 'model': Buffer, 'title': force_text(Buffer._meta.verbose_name) + " " + args[0], 'post_title': _('plan detail') } else: return {'active_tab': 'plandetail', 'model': None} rows = ( #. Translators: Translation included with Django GridFieldInteger('id', title=_('internal id'), key=True, editable=False, hidden=True), GridFieldText('item', title=_('item'), field_name='item__name', editable=False, formatter='detail', extra='"role":"input/item"'), GridFieldText('location', title=_('location'), field_name='location__name', editable=False, formatter='detail', extra='"role":"input/location"'), GridFieldInteger('operationplan__id', title=_('identifier'), editable=False), GridFieldText('operationplan__reference', title=_('reference'), editable=False), GridFieldText('operationplan__color', title=_('inventory status'), formatter='color', width='125', editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"'), GridFieldText('operationplan__type', title=_('type'), field_name='operationplan__type', editable=False), GridFieldText('operationplan__name', title=_('operation'), editable=False, field_name='operationplan__name', formatter='detail', extra='"role":"input/operation"'), GridFieldText('operationplan__operation__description', title=string_concat(_('operation'), ' - ', _('description')), editable=False, initially_hidden=True), GridFieldText('operationplan__operation__category', title=string_concat(_('operation'), ' - ', _('category')), editable=False, initially_hidden=True), GridFieldText('operationplan__operation__subcategory', title=string_concat(_('operation'), ' - ', _('subcategory')), editable=False, initially_hidden=True), GridFieldText('operationplan__operation__type', title=string_concat(_('operation'), ' - ', _('type')), initially_hidden=True), GridFieldDuration('operationplan__operation__duration', title=string_concat(_('operation'), ' - ', _('duration')), initially_hidden=True), GridFieldDuration('operationplan__operation__duration_per', title=string_concat(_('operation'), ' - ', _('duration per unit')), initially_hidden=True), GridFieldDuration('operationplan__operation__fence', title=string_concat(_('operation'), ' - ', _('release fence')), initially_hidden=True), GridFieldDuration('operationplan__operation__posttime', title=string_concat(_('operation'), ' - ', _('post-op time')), initially_hidden=True), GridFieldNumber('operationplan__operation__sizeminimum', title=string_concat(_('operation'), ' - ', _('size minimum')), initially_hidden=True), GridFieldNumber('operationplan__operation__sizemultiple', title=string_concat(_('operation'), ' - ', _('size multiple')), initially_hidden=True), GridFieldNumber('operationplan__operation__sizemaximum', title=string_concat(_('operation'), ' - ', _('size maximum')), initially_hidden=True), GridFieldInteger('operationplan__operation__priority', title=string_concat(_('operation'), ' - ', _('priority')), initially_hidden=True), GridFieldDateTime('operationplan__operation__effective_start', title=string_concat(_('operation'), ' - ', _('effective start')), initially_hidden=True), GridFieldDateTime('operationplan__operation__effective_end', title=string_concat(_('operation'), ' - ', _('effective end')), initially_hidden=True), GridFieldCurrency('operationplan__operation__cost', title=string_concat(_('operation'), ' - ', _('cost')), initially_hidden=True), GridFieldText('operationplan__operation__search', title=string_concat(_('operation'), ' - ', _('search mode')), initially_hidden=True), GridFieldText('operationplan__operation__source', title=string_concat(_('operation'), ' - ', _('source')), initially_hidden=True), GridFieldLastModified('operationplan__operation__lastmodified', title=string_concat(_('operation'), ' - ', _('last modified')), initially_hidden=True), GridFieldDateTime('flowdate', title=_('date'), editable=False, extra='"formatoptions":{"srcformat":"Y-m-d H:i:s","newformat":"Y-m-d H:i:s", "defaultValue":""}, "summaryType":"min"'), GridFieldNumber('quantity', title=_('quantity'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"'), GridFieldNumber('onhand', title=_('expected onhand'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"'), GridFieldText('operationplan__status', title=_('status'), editable=False, field_name='operationplan__status'), GridFieldNumber('operationplan__criticality', title=_('criticality'), field_name='operationplan__criticality', editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"min"'), GridFieldDuration('operationplan__delay', title=_('delay'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"max"'), GridFieldNumber('operationplan__quantity', title=_('operationplan quantity'), editable=False, extra='"formatoptions":{"defaultValue":""}, "summaryType":"sum"'), GridFieldText('demand', title=_('demands'), formatter='demanddetail', extra='"role":"input/demand"', width=300, editable=False, sortable=False), GridFieldBool('feasible', title=_('feasible'), editable=False, initially_hidden=True, search=False), # Optional fields referencing the item GridFieldText('item__description', title=string_concat(_('item'), ' - ', _('description')), initially_hidden=True, editable=False), GridFieldText('item__category', title=string_concat(_('item'), ' - ', _('category')), initially_hidden=True, editable=False), GridFieldText('item__subcategory', title=string_concat(_('item'), ' - ', _('subcategory')), initially_hidden=True, editable=False), GridFieldNumber('item__cost', title=string_concat(_('item'), ' - ', _('cost')), initially_hidden=True, editable=False), GridFieldText('item__owner', title=string_concat(_('item'), ' - ', _('owner')), field_name='item__owner__name', initially_hidden=True, editable=False), GridFieldText('item__source', title=string_concat(_('item'), ' - ', _('source')), initially_hidden=True, editable=False), GridFieldLastModified('item__lastmodified', title=string_concat(_('item'), ' - ', _('last modified')), initially_hidden=True, editable=False), # Optional fields referencing the location GridFieldText('location__description', title=string_concat(_('location'), ' - ', _('description')), initially_hidden=True, editable=False), GridFieldText('location__category', title=string_concat(_('location'), ' - ', _('category')), initially_hidden=True, editable=False), GridFieldText('location__subcategory', title=string_concat(_('location'), ' - ', _('subcategory')), initially_hidden=True, editable=False), GridFieldText('location__available', title=string_concat(_('location'), ' - ', _('available')), initially_hidden=True, field_name='location__available__name', formatter='detail', extra='"role":"input/calendar"', editable=False), GridFieldText('location__owner', title=string_concat(_('location'), ' - ', _('owner')), initially_hidden=True, field_name='location__owner__name', formatter='detail', extra='"role":"input/location"', editable=False), GridFieldText('location__source', title=string_concat(_('location'), ' - ', _('source')), initially_hidden=True, editable=False), GridFieldLastModified('location__lastmodified', title=string_concat(_('location'), ' - ', _('last modified')), initially_hidden=True, editable=False), GridFieldBool('feasible', title=_('feasible'), editable=False, initially_hidden=True, search=False), )
class ItemSupplierList(GridReport): title = _("item suppliers") basequeryset = ItemSupplier.objects.all() model = ItemSupplier frozenColumns = 1 help_url = "modeling-wizard/purchasing/item-suppliers.html" rows = ( GridFieldInteger( "id", title=_("identifier"), key=True, formatter="detail", extra='"role":"input/itemsupplier"', initially_hidden=True, ), GridFieldHierarchicalText( "item", title=_("item"), field_name="item__name", formatter="detail", extra='"role":"input/item"', model=Item, ), GridFieldHierarchicalText( "location", title=_("location"), field_name="location__name", formatter="detail", extra='"role":"input/location"', model=Location, ), GridFieldText( "supplier", title=_("supplier"), field_name="supplier__name", formatter="detail", extra='"role":"input/supplier"', ), GridFieldDuration("leadtime", title=_("lead time")), GridFieldNumber("sizeminimum", title=_("size minimum")), GridFieldNumber("sizemultiple", title=_("size multiple")), GridFieldNumber("sizemaximum", title=_("size maximum"), initially_hidden=True), GridFieldCurrency("cost", title=_("cost")), GridFieldInteger("priority", title=_("priority")), GridFieldDuration("fence", title=_("fence"), initially_hidden=True), GridFieldDateTime("effective_start", title=_("effective start"), initially_hidden=True), GridFieldDateTime("effective_end", title=_("effective end"), initially_hidden=True), GridFieldText( "resource", title=_("resource"), field_name="resource__name", formatter="detail", extra='"role":"input/resource"', initially_hidden=True, ), GridFieldNumber("resource_qty", title=_("resource quantity"), initially_hidden=True), GridFieldText("source", title=_("source"), initially_hidden=True), GridFieldLastModified("lastmodified"), # Optional fields referencing the item GridFieldText( "item__type", title=format_lazy("{} - {}", _("item"), _("type")), initially_hidden=True, editable=False, ), GridFieldText( "item__description", title=format_lazy("{} - {}", _("item"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "item__category", title=format_lazy("{} - {}", _("item"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "item__subcategory", title=format_lazy("{} - {}", _("item"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldCurrency( "item__cost", title=format_lazy("{} - {}", _("item"), _("cost")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__volume", title=format_lazy("{} - {}", _("item"), _("volume")), initially_hidden=True, editable=False, ), GridFieldNumber( "item__weight", title=format_lazy("{} - {}", _("item"), _("weight")), initially_hidden=True, editable=False, ), GridFieldText( "item__owner", title=format_lazy("{} - {}", _("item"), _("owner")), field_name="item__owner__name", initially_hidden=True, editable=False, ), GridFieldText( "item__source", title=format_lazy("{} - {}", _("item"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "item__lastmodified", title=format_lazy("{} - {}", _("item"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the location GridFieldText( "location__description", title=format_lazy("{} - {}", _("location"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "location__category", title=format_lazy("{} - {}", _("location"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "location__subcategory", title=format_lazy("{} - {}", _("location"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "location__available", title=format_lazy("{} - {}", _("location"), _("available")), initially_hidden=True, field_name="location__available__name", formatter="detail", extra='"role":"input/calendar"', editable=False, ), GridFieldText( "location__owner", title=format_lazy("{} - {}", _("location"), _("owner")), initially_hidden=True, field_name="location__owner__name", formatter="detail", extra='"role":"input/location"', editable=False, ), GridFieldText( "location__source", title=format_lazy("{} - {}", _("location"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "location__lastmodified", title=format_lazy("{} - {}", _("location"), _("last modified")), initially_hidden=True, editable=False, ), # Optional fields referencing the supplier GridFieldText( "supplier__description", title=format_lazy("{} - {}", _("supplier"), _("description")), initially_hidden=True, editable=False, ), GridFieldText( "supplier__category", title=format_lazy("{} - {}", _("supplier"), _("category")), initially_hidden=True, editable=False, ), GridFieldText( "supplier__subcategory", title=format_lazy("{} - {}", _("supplier"), _("subcategory")), initially_hidden=True, editable=False, ), GridFieldText( "supplier__available", title=format_lazy("{} - {}", _("supplier"), _("available")), field_name="supplier__available__name", formatter="detail", extra='"role":"input/calendar"', initially_hidden=True, editable=False, ), GridFieldText( "supplier__source", title=format_lazy("{} - {}", _("supplier"), _("source")), initially_hidden=True, editable=False, ), GridFieldLastModified( "supplier__lastmodified", title=format_lazy("{} - {}", _("supplier"), _("last modified")), initially_hidden=True, editable=False, ), ) @classmethod def initialize(reportclass, request): if reportclass._attributes_added != 2: reportclass._attributes_added = 2 reportclass.attr_sql = "" # Adding custom item attributes for f in getAttributeFields(Item, related_name_prefix="item", initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += "item.%s, " % f.name.split("__")[-1] # Adding custom location attributes for f in getAttributeFields(Location, related_name_prefix="location", initially_hidden=True): reportclass.rows += (f, ) reportclass.attr_sql += "location.%s, " % f.name.split( "__")[-1]