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]
