293 lines
12 KiB
Python
293 lines
12 KiB
Python
from functools import wraps
|
|
from trytond.model import ModelSingleton, ModelSQL, ModelView, fields
|
|
from trytond.report import Report
|
|
from trytond.pool import Pool, PoolMeta
|
|
from trytond.pyson import Bool, Eval, Id, If, PYSONEncoder
|
|
from trytond.model import (ModelSQL, ModelView)
|
|
from trytond.tools import (cursor_dict, is_full_text, lstrip_wildcard)
|
|
from trytond.transaction import Transaction, inactive_records
|
|
from decimal import getcontext, Decimal, ROUND_HALF_UP
|
|
from sql.aggregate import Count, Max, Min, Sum, Avg, BoolOr
|
|
from sql.conditionals import Case
|
|
from sql.functions import CurrentTimestamp, DateTrunc, Abs
|
|
from sql import Column, Literal, Union, Select
|
|
from trytond.wizard import Button, StateTransition, StateView, Wizard, StateAction
|
|
from itertools import chain, groupby
|
|
from operator import itemgetter
|
|
import datetime
|
|
import logging
|
|
import json
|
|
import jwt
|
|
from collections import defaultdict
|
|
from trytond.exceptions import UserWarning, UserError
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
class OpenPosition(ModelSQL):
|
|
"Open position"
|
|
__name__ = 'open.position'
|
|
product = fields.Many2One('product.product', "Product", required=True)
|
|
purchase = fields.Many2One('purchase.purchase',"Purchase")
|
|
line = fields.Many2One('purchase.line',"Line")
|
|
supplier = fields.Many2One('party.party', "Supplier", help="Optional counterparty aggregation")
|
|
currency = fields.Many2One('currency.currency', "Currency", required=True)
|
|
uom = fields.Many2One('product.uom', "Unit of Measure")
|
|
period_start = fields.Date("Period start")
|
|
period_end = fields.Date("Period end")
|
|
type = fields.Selection([
|
|
('priced', 'Priced'),
|
|
('hedge', 'Hedge'),
|
|
('physic', 'Physic'),
|
|
('shipped', 'Shipped'),
|
|
('open', 'Open')
|
|
], "Type")
|
|
physical_qty = fields.Numeric("Priced Quantity", digits=(16, 6))
|
|
hedged_qty = fields.Numeric("Hedged Quantity", digits=(16, 6))
|
|
net_exposure = fields.Numeric("Open Quantity", digits=(16, 6))
|
|
price = fields.Numeric("Price", digits=(16, 6))
|
|
amount = fields.Numeric("Amount", digits=(16, 6))
|
|
curve = fields.Many2One('price.price')
|
|
mtm_price = fields.Numeric("Mtm price", digits=(16, 6))
|
|
mtm = fields.Numeric("Mtm amount", digits=(16, 2),help="Unrealised MTM for open quantities (in currency)")
|
|
sensitivity = fields.Numeric("Sensitivity (per unit)", digits=(16, 6),help="MTM change per 1 unit change in price")
|
|
|
|
@classmethod
|
|
def create_from_sale_line(cls, line):
|
|
Date = Pool().get('ir.date')
|
|
OpenPosition = Pool().get('open.position')
|
|
op = OpenPosition.search([('sale_line','=',line.id)])
|
|
if op:
|
|
OpenPosition.delete(op)
|
|
|
|
vals = {
|
|
'product': line.product.id,
|
|
'sale': line.sale.id,
|
|
'sale_line': line.id,
|
|
'client': line.sale.party.id,
|
|
'currency': line.sale.currency.id,
|
|
'uom': line.unit.id,
|
|
'period_start': line.from_del,
|
|
'period_end': line.to_del
|
|
}
|
|
|
|
##OPEN PART###
|
|
vals['type'] = 'open'
|
|
vals['physical_qty'] = round(line.quantity_theorical - Decimal(line.quantity),5)
|
|
if vals['physical_qty'] == Decimal(0) and line.lots and len(line.lots)==1:
|
|
vals['physical_qty'] = Decimal(line.quantity)
|
|
vals['physical_qty'] = Decimal(vals['physical_qty'])*Decimal(-1)
|
|
vals['hedged_qty'] = Decimal(0)
|
|
vals['net_exposure'] = vals['physical_qty']
|
|
vals['price'] = line.unit_price
|
|
vals['amount'] = round(vals['price'] * vals['physical_qty'],2)
|
|
if line.mtm:
|
|
for mt in line.mtm:
|
|
vals['curve'] = mt.price_index.id
|
|
vals['mtm_price'] = round(Decimal(mt.price_index.get_price(Date.today(),line.unit,line.currency,True)),4)
|
|
vals['mtm'] = round(vals['mtm_price'] * vals['physical_qty'],2)
|
|
OpenPosition.create([vals])
|
|
else:
|
|
OpenPosition.create([vals])
|
|
|
|
@classmethod
|
|
def create_from_purchase_line(cls, line):
|
|
OpenPosition = Pool().get('open.position')
|
|
Date = Pool().get('ir.date')
|
|
op = OpenPosition.search([('line','=',line.id)])
|
|
if op:
|
|
OpenPosition.delete(op)
|
|
|
|
vals = {
|
|
'product': line.product.id,
|
|
'purchase': line.purchase.id,
|
|
'line': line.id,
|
|
'supplier': line.purchase.party.id,
|
|
'currency': line.purchase.currency.id,
|
|
'uom': line.unit.id,
|
|
'period_start': line.from_del,
|
|
'period_end': line.to_del
|
|
}
|
|
|
|
##OPEN PART###
|
|
vals['type'] = 'open'
|
|
vals['physical_qty'] = round(line.quantity_theorical - Decimal(line.quantity),5)
|
|
if vals['physical_qty'] == Decimal(0) and line.lots and len(line.lots)==1:
|
|
vals['physical_qty'] = Decimal(line.quantity)
|
|
vals['hedged_qty'] = Decimal(0)
|
|
vals['net_exposure'] = vals['physical_qty']
|
|
vals['price'] = line.unit_price
|
|
vals['amount'] = round(vals['price'] * vals['physical_qty'],2)
|
|
if line.mtm:
|
|
for mt in line.mtm:
|
|
vals['curve'] = mt.price_index.id
|
|
vals['mtm_price'] = round(Decimal(mt.price_index.get_price(Date.today(),line.unit,line.currency,True)),4)
|
|
vals['mtm'] = round(vals['mtm_price'] * vals['physical_qty'],2)
|
|
OpenPosition.create([vals])
|
|
else:
|
|
OpenPosition.create([vals])
|
|
##PHYSIC PART###
|
|
for lot in line.lots:
|
|
if not lot.invoice_line and not lot.invoice_line_prov and lot.lot_type == 'physic':
|
|
if lot.GetShipment('in'):
|
|
vals['type'] = 'shipped'
|
|
else:
|
|
vals['type'] = 'physic'
|
|
vals['physical_qty'] = lot.get_current_quantity_converted()
|
|
vals['hedged_qty'] = Decimal(0)
|
|
vals['net_exposure'] = vals['physical_qty']
|
|
vals['price'] = lot.get_lot_price()
|
|
vals['amount'] = round(vals['price'] * vals['physical_qty'],2)
|
|
if line.mtm:
|
|
for mt in line.mtm:
|
|
vals['curve'] = mt.price_index.id
|
|
vals['mtm_price'] = round(Decimal(mt.price_index.get_price(Date.today(),line.unit,line.currency,True)),4)
|
|
vals['mtm'] = round(vals['mtm_price'] * vals['physical_qty'],2)
|
|
OpenPosition.create([vals])
|
|
else:
|
|
OpenPosition.create([vals])
|
|
##DERIVATIVE PART###
|
|
if line.derivatives:
|
|
for d in line.derivatives:
|
|
vals['type'] = 'hedge'
|
|
vals['physical_qty'] = Decimal(0)
|
|
vals['hedged_qty'] = -d.quantity
|
|
vals['net_exposure'] = d.quantity
|
|
vals['price'] = d.price
|
|
vals['amount'] = round(vals['price'] * d.quantity,2)
|
|
vals['curve'] = d.price_index.id
|
|
vals['mtm_price'] = round(Decimal(d.price_index.get_price(Date.today(),line.unit,line.currency,True)),4)
|
|
vals['mtm'] = round(vals['mtm_price'] * d.quantity,2)
|
|
OpenPosition.create([vals])
|
|
|
|
class OpenPositionReport(
|
|
ModelSQL, ModelView):
|
|
"Open position report"
|
|
__name__ = 'open.position.report'
|
|
product = fields.Many2One('product.product', "Product", required=True)
|
|
purchase = fields.Many2One('purchase.purchase',"Purchase")
|
|
line = fields.Many2One('purchase.line',"Line")
|
|
sale = fields.Many2One('sale.sale',"Sale")
|
|
sale_line = fields.Many2One('sale.line',"Sale Line")
|
|
supplier = fields.Many2One('party.party', "Supplier")
|
|
client = fields.Many2One('party.party', "Client")
|
|
currency = fields.Many2One('currency.currency', "Currency", required=True)
|
|
uom = fields.Many2One('product.uom', "Unit of Measure")
|
|
period_start = fields.Date("Period start")
|
|
period_end = fields.Date("Period end")
|
|
type = fields.Selection([
|
|
('priced', 'Priced'),
|
|
('hedge', 'Hedge'),
|
|
('physic', 'Physic'),
|
|
('shipped', 'Shipped'),
|
|
('open', 'Open')
|
|
], "Type")
|
|
physical_qty = fields.Numeric("Priced Quantity", digits=(16, 5))
|
|
hedged_qty = fields.Numeric("Hedged Quantity", digits=(16, 5))
|
|
net_exposure = fields.Numeric("Open Quantity", digits=(16, 5))
|
|
price = fields.Numeric("Price", digits=(16, 4))
|
|
amount = fields.Numeric("Amount", digits=(16, 2))
|
|
curve = fields.Many2One('price.price')
|
|
mtm_price = fields.Numeric("Mtm price", digits=(16, 4))
|
|
mtm = fields.Numeric("Mtm amount", digits=(16, 2),help="Unrealised MTM for open quantities (in currency)")
|
|
pnl = fields.Numeric("Pnl", digits=(16, 2))
|
|
sensitivity = fields.Numeric("Sensitivity (per unit)", digits=(16, 1),help="MTM change per 1 unit change in price")
|
|
|
|
@classmethod
|
|
def table_query(cls):
|
|
OpenPosition = Pool().get('open.position')
|
|
op = OpenPosition.__table__()
|
|
|
|
context = Transaction().context
|
|
supplier = context.get('supplier')
|
|
purchase = context.get('purchase')
|
|
client = context.get('client')
|
|
sale = context.get('sale')
|
|
product = context.get('product')
|
|
asof = context.get('asof')
|
|
todate = context.get('todate')
|
|
state = context.get('state')
|
|
|
|
wh = Literal(True)
|
|
if supplier:
|
|
wh &= (op.supplier == supplier)
|
|
if client:
|
|
wh &= (op.client == client)
|
|
if product:
|
|
wh &= (op.product == product)
|
|
if purchase:
|
|
wh &= (op.purchase == purchase)
|
|
if sale:
|
|
wh &= (op.sale == sale)
|
|
# if asof and todate:
|
|
# wh &= (pu.purchase_date >= asof) & (pu.purchase_date <= todate)
|
|
|
|
query = (
|
|
op
|
|
.select(
|
|
Literal(0).as_('create_uid'),
|
|
CurrentTimestamp().as_('create_date'),
|
|
Literal(0).as_('write_uid'),
|
|
Literal(0).as_('write_date'),
|
|
op.id.as_('id'),
|
|
op.product.as_('product'),
|
|
op.supplier.as_('supplier'),
|
|
op.client.as_('client'),
|
|
op.purchase.as_('purchase'),
|
|
op.sale.as_('sale'),
|
|
op.line.as_('line'),
|
|
op.sale_line.as_('sale_line'),
|
|
op.type.as_('type'),
|
|
op.currency.as_('currency'),
|
|
op.uom.as_('uom'),
|
|
op.period_start.as_('period_start'),
|
|
op.period_end.as_('period_end'),
|
|
op.physical_qty.as_('physical_qty'),
|
|
op.hedged_qty.as_('hedged_qty'),
|
|
op.net_exposure.as_('net_exposure'),
|
|
op.price.as_('price'),
|
|
op.amount.as_('amount'),
|
|
op.curve.as_('curve'),
|
|
op.mtm_price.as_('mtm_price'),
|
|
op.mtm.as_('mtm'),
|
|
(op.amount-op.mtm).as_('pnl'),
|
|
op.sensitivity.as_('sensitivity'),
|
|
where=wh
|
|
)
|
|
)
|
|
|
|
return query
|
|
|
|
class OpenPositionContext(ModelView):
|
|
"Open Position Context"
|
|
__name__ = 'open.position.context'
|
|
|
|
asof = fields.Date("As of")
|
|
todate = fields.Date("To")
|
|
supplier = fields.Many2One('party.party',"Supplier")
|
|
client = fields.Many2One('party.party',"Client")
|
|
product = fields.Many2One('product.product',"Product")
|
|
purchase = fields.Many2One('purchase.purchase', "Purchase")
|
|
sale = fields.Many2One('sale.sale',"Sale")
|
|
state = fields.Selection([
|
|
('all', 'All'),
|
|
('open', 'Open'),
|
|
('fixed', 'Fixed'),
|
|
('hedged', 'Hedged')
|
|
], 'State')
|
|
|
|
@classmethod
|
|
def default_asof(cls):
|
|
pool = Pool()
|
|
Date = pool.get('ir.date')
|
|
return Date.today().replace(day=1,month=1,year=1999)
|
|
|
|
@classmethod
|
|
def default_todate(cls):
|
|
pool = Pool()
|
|
Date = pool.get('ir.date')
|
|
return Date.today()
|
|
|
|
@classmethod
|
|
def default_state(cls):
|
|
return 'all'
|