367 lines
14 KiB
Python
367 lines
14 KiB
Python
import stdnum.exceptions
|
|
from sql import Column, Literal
|
|
import datetime
|
|
from sql.aggregate import Count, Max, Min, Sum, Avg, BoolOr
|
|
from sql.conditionals import Case
|
|
from stdnum import get_cc_module
|
|
from sql.functions import CharLength, CurrentTimestamp, DateTrunc, Extract
|
|
from trytond.i18n import gettext
|
|
from trytond.model import (
|
|
DeactivableMixin, Index, ModelSQL, ModelView, MultiValueMixin, Unique,
|
|
ValueMixin, convert_from, fields, sequence_ordered)
|
|
from trytond.model.exceptions import AccessError
|
|
from trytond.pool import Pool
|
|
from trytond.pyson import Bool, Eval
|
|
from trytond.tools import is_full_text, lstrip_wildcard
|
|
from trytond.transaction import Transaction, inactive_records
|
|
from trytond.wizard import Button, StateTransition, StateView, Wizard
|
|
from decimal import Decimal
|
|
import logging
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
class PositionVar(ModelSQL, ModelView):
|
|
"Position var"
|
|
__name__ = 'risk.position.var'
|
|
|
|
var = fields.Many2One('risk.var',"Var")
|
|
position = fields.Many2One('risk.position',"Position")
|
|
d_var = fields.Numeric("VaR")
|
|
d_cvar = fields.Numeric("CVaR")
|
|
d_cdar = fields.Numeric("CDaR")
|
|
whatif = fields.Numeric("Whatif")
|
|
|
|
@classmethod
|
|
def default_whatif(cls):
|
|
return 1
|
|
|
|
|
|
class Position(ModelSQL, ModelView):
|
|
"Position"
|
|
__name__ = 'risk.position'
|
|
|
|
commodity = fields.Char("Commodity")
|
|
type = fields.Char("Type")
|
|
trader = fields.Char("Trader")
|
|
reference = fields.Char("Reference")
|
|
product = fields.Char("Product")
|
|
origin = fields.Char("Origin")
|
|
future_market = fields.Char("Future market")
|
|
currency = fields.Char("Currency")
|
|
month = fields.Char("Month")
|
|
ct_type = fields.Char("Ct type")
|
|
quantity = fields.Numeric("Quantity")
|
|
basis = fields.Numeric("Basis")
|
|
price = fields.Numeric("Price")
|
|
pricing_type = fields.Char("Pricing type")
|
|
var = fields.Numeric("VaR")
|
|
cvar = fields.Numeric("CVaR")
|
|
cdar = fields.Numeric("CDaR")
|
|
whatif = fields.Numeric("Whatif")
|
|
|
|
@classmethod
|
|
def default_whatif(cls):
|
|
return 0
|
|
|
|
@classmethod
|
|
def search_rec_name(cls, name, clause):
|
|
_, operator, operand, *extra = clause
|
|
if operator.startswith('!') or operator.startswith('not '):
|
|
bool_op = 'AND'
|
|
else:
|
|
bool_op = 'OR'
|
|
code_value = operand
|
|
if operator.endswith('like') and is_full_text(operand):
|
|
code_value = lstrip_wildcard(operand)
|
|
return [bool_op,
|
|
('commodity', operator, operand, *extra),
|
|
('trader', operator, operand, *extra),
|
|
('product', operator, operand, *extra),
|
|
('origin', operator, operand, *extra),
|
|
]
|
|
|
|
class PositionReport(ModelSQL, ModelView):
|
|
"Position report"
|
|
__name__ = 'risk.position.report'
|
|
|
|
commodity = fields.Char("Commodity")
|
|
type = fields.Char("Type")
|
|
trader = fields.Char("Trader")
|
|
reference = fields.Char("Reference")
|
|
product = fields.Char("Product")
|
|
origin = fields.Char("Origin")
|
|
future_market = fields.Char("Future market")
|
|
currency = fields.Char("Currency")
|
|
month = fields.Char("Month")
|
|
ct_type = fields.Char("Ct type")
|
|
quantity = fields.Numeric("Quantity")
|
|
basis = fields.Numeric("Basis")
|
|
price = fields.Numeric("Price")
|
|
pricing_type = fields.Char("Pricing type")
|
|
var = fields.Numeric("VaR")
|
|
cvar = fields.Numeric("CVaR")
|
|
cdar = fields.Numeric("CDaR")
|
|
var_amount = fields.Numeric("VaR amt")
|
|
cvar_amount = fields.Numeric("CVaR amt")
|
|
cdar_amount = fields.Numeric("CDaR amt")
|
|
whatif = fields.Numeric("Whatif")
|
|
|
|
@classmethod
|
|
def table_query(cls):
|
|
pool = Pool()
|
|
Pos = pool.get('risk.position')
|
|
pos = Pos.__table__()
|
|
Pos_var = pool.get('risk.position.var')
|
|
pos2 = Pos_var.__table__()
|
|
context = Transaction().context
|
|
trader = context.get('trader')
|
|
product = context.get('product')
|
|
month = context.get('month')
|
|
market = context.get('market')
|
|
asof = context.get('asof')
|
|
todate = context.get('todate')
|
|
var = context.get('var')
|
|
groupby = context.get('groupby')
|
|
wh = (((pos.quantity >= 0) & (pos.create_date >= asof) & ((pos.create_date-datetime.timedelta(1)) <= todate)))
|
|
if trader:
|
|
wh &= (pos.trader == trader)
|
|
if product:
|
|
wh &= (pos.product == product)
|
|
if month:
|
|
wh &= (pos.month == month)
|
|
if market:
|
|
wh &= (pos.future_market == market)
|
|
if var:
|
|
wh &= (pos2.var == var)
|
|
|
|
if groupby == None:
|
|
query = pos.join(pos2,'LEFT',condition=pos.id == pos2.position).select(
|
|
Literal(0).as_('create_uid'),
|
|
CurrentTimestamp().as_('create_date'),
|
|
Literal(None).as_('write_uid'),
|
|
Literal(None).as_('write_date'),
|
|
pos.id.as_('id'),
|
|
pos.commodity.as_('commodity'),
|
|
pos.type.as_('type'),
|
|
pos.trader.as_('trader'),
|
|
pos.reference.as_('reference'),
|
|
pos.product.as_('product'),
|
|
pos.origin.as_('origin'),
|
|
pos.future_market.as_('future_market'),
|
|
pos.currency.as_('currency'),
|
|
pos.month.as_('month'),
|
|
pos.ct_type.as_('ct_type'),
|
|
pos.quantity.as_('quantity'),
|
|
pos.basis.as_('basis'),
|
|
pos.price.as_('price'),
|
|
pos.pricing_type.as_('pricing_type'),
|
|
pos2.d_var.as_('var'),
|
|
pos2.d_cvar.as_('cvar'),
|
|
pos2.d_cdar.as_('cdar'),
|
|
(pos2.d_var * pos.quantity * pos.price * pos2.whatif).as_('var_amount'),
|
|
(pos2.d_cvar * pos.quantity * pos.price).as_('cvar_amount'),
|
|
(pos2.d_cdar * pos.quantity * pos.price).as_('cdar_amount'),
|
|
(pos.whatif).as_('whatif'),
|
|
where=wh)
|
|
elif groupby == 'commo':
|
|
query = pos.join(pos2,'LEFT',condition=pos.id == pos2.position).select(
|
|
Literal(0).as_('create_uid'),
|
|
CurrentTimestamp().as_('create_date'),
|
|
Literal(None).as_('write_uid'),
|
|
Literal(None).as_('write_date'),
|
|
Max(pos.id).as_('id'),
|
|
pos.commodity.as_('commodity'),
|
|
Max(pos.type).as_('type'),
|
|
Max(pos.trader).as_('trader'),
|
|
Max(pos.reference).as_('reference'),
|
|
Max(pos.product).as_('product'),
|
|
Max(pos.origin).as_('origin'),
|
|
Max(pos.future_market).as_('future_market'),
|
|
Max(pos.currency).as_('currency'),
|
|
Max(pos.month).as_('month'),
|
|
Max(pos.ct_type).as_('ct_type'),
|
|
Sum(pos.quantity).as_('quantity'),
|
|
Avg(pos.basis).as_('basis'),
|
|
Avg(pos.price).as_('price'),
|
|
Max(pos.pricing_type).as_('pricing_type'),
|
|
Avg(pos2.d_var).as_('var'),
|
|
Avg(pos2.d_cvar).as_('cvar'),
|
|
Avg(pos2.d_cdar).as_('cdar'),
|
|
Sum(pos2.d_var * pos.quantity * pos.price * pos2.whatif).as_('var_amount'),
|
|
Sum(pos2.d_cvar * pos.quantity * pos.price).as_('cvar_amount'),
|
|
Sum(pos2.d_cdar * pos.quantity * pos.price).as_('cdar_amount'),
|
|
Avg(pos.whatif).as_('whatif'),
|
|
where=wh,
|
|
group_by=[pos.commodity])
|
|
elif groupby == 'market':
|
|
query = pos.join(pos2,'LEFT',condition=pos.id == pos2.position).select(
|
|
Literal(0).as_('create_uid'),
|
|
CurrentTimestamp().as_('create_date'),
|
|
Literal(None).as_('write_uid'),
|
|
Literal(None).as_('write_date'),
|
|
Max(pos.id).as_('id'),
|
|
Max(pos.commodity).as_('commodity'),
|
|
Max(pos.type).as_('type'),
|
|
Max(pos.trader).as_('trader'),
|
|
Max(pos.reference).as_('reference'),
|
|
Max(pos.product).as_('product'),
|
|
Max(pos.origin).as_('origin'),
|
|
pos.future_market.as_('future_market'),
|
|
Max(pos.currency).as_('currency'),
|
|
Max(pos.month).as_('month'),
|
|
Max(pos.ct_type).as_('ct_type'),
|
|
Sum(pos.quantity).as_('quantity'),
|
|
Avg(pos.basis).as_('basis'),
|
|
Avg(pos.price).as_('price'),
|
|
Max(pos.pricing_type).as_('pricing_type'),
|
|
Avg(pos2.d_var).as_('var'),
|
|
Avg(pos2.d_cvar).as_('cvar'),
|
|
Avg(pos2.d_cdar).as_('cdar'),
|
|
Sum(pos2.d_var * pos.quantity * pos.price * pos2.whatif).as_('var_amount'),
|
|
Sum(pos2.d_cvar * pos.quantity * pos.price).as_('cvar_amount'),
|
|
Sum(pos2.d_cdar * pos.quantity * pos.price).as_('cdar_amount'),
|
|
Avg(pos.whatif).as_('whatif'),
|
|
where=wh,
|
|
group_by=[pos.future_market])
|
|
elif groupby == 'month':
|
|
query = pos.join(pos2,'LEFT',condition=pos.id == pos2.position).select(
|
|
Literal(0).as_('create_uid'),
|
|
CurrentTimestamp().as_('create_date'),
|
|
Literal(None).as_('write_uid'),
|
|
Literal(None).as_('write_date'),
|
|
Max(pos.id).as_('id'),
|
|
Max(pos.commodity).as_('commodity'),
|
|
Max(pos.type).as_('type'),
|
|
Max(pos.trader).as_('trader'),
|
|
Max(pos.reference).as_('reference'),
|
|
Max(pos.product).as_('product'),
|
|
Max(pos.origin).as_('origin'),
|
|
pos.future_market.as_('future_market'),
|
|
Max(pos.currency).as_('currency'),
|
|
pos.month.as_('month'),
|
|
Max(pos.ct_type).as_('ct_type'),
|
|
Sum(pos.quantity).as_('quantity'),
|
|
Avg(pos.basis).as_('basis'),
|
|
Avg(pos.price).as_('price'),
|
|
Max(pos.pricing_type).as_('pricing_type'),
|
|
Avg(pos2.d_var).as_('var'),
|
|
Avg(pos2.d_cvar).as_('cvar'),
|
|
Avg(pos2.d_cdar).as_('cdar'),
|
|
Sum(pos2.d_var * pos.quantity * pos.price * pos2.whatif).as_('var_amount'),
|
|
Sum(pos2.d_cvar * pos.quantity * pos.price).as_('cvar_amount'),
|
|
Sum(pos2.d_cdar * pos.quantity * pos.price).as_('cdar_amount'),
|
|
Avg(pos.whatif).as_('whatif'),
|
|
where=wh,
|
|
group_by=[pos.future_market,pos.month])
|
|
return query
|
|
|
|
@classmethod
|
|
def view_attributes(cls):
|
|
return super().view_attributes() + [
|
|
('/tree/field[@name="trader"]', 'tree_invisible',
|
|
(Eval('groupby')!=None)),
|
|
('/tree/field[@name="product"]', 'tree_invisible',
|
|
(Eval('groupby')!=None)),
|
|
('/tree/field[@name="future_market"]', 'tree_invisible',
|
|
(Eval('groupby')=='commo')),
|
|
('/tree/field[@name="month"]', 'tree_invisible',
|
|
(Eval('groupby').in_(['market', 'commo']))),
|
|
]
|
|
|
|
class PositionProcess(ModelSQL, ModelView):
|
|
"Position process"
|
|
__name__ = 'risk.position.process'
|
|
|
|
future_market = fields.Char("Future market")
|
|
month = fields.Char("Month")
|
|
quantity = fields.Numeric("Quantity")
|
|
|
|
@classmethod
|
|
def table_query(cls):
|
|
pool = Pool()
|
|
Pos = pool.get('risk.position')
|
|
pos = Pos.__table__()
|
|
wh = ((pos.pricing_type != 'Priced') & (pos.ct_type != 'Inventory') & (pos.quantity > 0))
|
|
query = pos.select(
|
|
Literal(0).as_('create_uid'),
|
|
CurrentTimestamp().as_('create_date'),
|
|
Literal(None).as_('write_uid'),
|
|
Literal(None).as_('write_date'),
|
|
Max(pos.id).as_('id'),
|
|
pos.future_market.as_('future_market'),
|
|
pos.month.as_('month'),
|
|
Sum(pos.quantity).as_('quantity'),
|
|
where=wh,
|
|
group_by=[pos.future_market,pos.month])
|
|
return query
|
|
|
|
class PositionContext(ModelView):
|
|
"Lot Context"
|
|
__name__ = 'risk.position.context'
|
|
|
|
asof = fields.Date("As of")
|
|
todate = fields.Date("To")
|
|
trader = fields.Selection([
|
|
(None, ''),
|
|
("Simon Bourqui", "Simon Bourqui"),
|
|
("Jean-Christophe Mani", "Jean-Christophe Mani"),
|
|
("Gregoire Davidoff", "Gregoire Davidoff"),
|
|
("Pablo Rapun","Pablo Rapun"),
|
|
("Riccardo Zanin","Riccardo Zanin"),
|
|
("Antoine Bonnot","Antoine Bonnot"),
|
|
("Eric Bourgeois","Eric Bourgeois")
|
|
],"Trader")
|
|
product = fields.Selection([
|
|
(None, ''),
|
|
("Coffee", "Coffee"),
|
|
("Cocoa", "Cocoa"),
|
|
],"Product")
|
|
month = fields.Selection([
|
|
(None, ''),
|
|
("Mar-24", "Mar-24"),
|
|
("Jul-24", "Jul-24"),
|
|
("Mar-26", "Mar-26"),
|
|
("Dec-24","Dec-24"),
|
|
("Dec-23","Dec-23"),
|
|
("Nov-24","Nov-24"),
|
|
("Dec-25","Dec-25"),
|
|
("Sep-24", "Sep-24"),
|
|
("Jul-25", "Jul-25"),
|
|
("Mar-25", "Mar-25"),
|
|
("May-25","May-25"),
|
|
("May-24","May-24"),
|
|
("Sep-25","Sep-25")
|
|
],"Month")
|
|
market = fields.Selection([
|
|
(None, ''),
|
|
("Coffee ICE US", "Coffee ICE US"),
|
|
("Cocoa ICE US", "Cocoa ICE US"),
|
|
("Coffee ICE EU", "Coffee ICE EU"),
|
|
("Cocoa ICE EU", "Cocoa ICE EU")
|
|
],"Market")
|
|
var = fields.Many2One('risk.var',"VaR template",domain=[('execute','=',True)])
|
|
groupby = fields.Selection([
|
|
(None, ''),
|
|
("commo", "Commodity"),
|
|
("market", "Market"),
|
|
("month", "Month"),
|
|
],"Group by")
|
|
|
|
@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_var(cls):
|
|
Var = Pool().get('risk.var')
|
|
var = Var.search([('execute','=',True)])
|
|
if var:
|
|
return var[0].id |