from sql import Literal, Null, Union, Window
from sql.aggregate import Count, Max, Min, Sum
from sql.conditionals import Case, Coalesce
from sql.functions import CurrentTimestamp, RowNumber

from trytond.model import ModelSQL, ModelView, fields
from trytond.pool import Pool
from trytond.transaction import Transaction


PHYSICAL_VALUATION_TYPES = [
    'priced',
    'pur. priced',
    'pur. efp',
    'sale priced',
    'sale efp',
    'line fee',
    'pur. fee',
    'sale fee',
    'shipment fee',
    'market',
]
DERIVATIVE_VALUATION_TYPES = ['derivative']
MTM_VALUATION_TYPES = ['mtm', 'pur. mtm', 'sale mtm']
ALL_VALUATION_TYPES = (
    PHYSICAL_VALUATION_TYPES + DERIVATIVE_VALUATION_TYPES
    + MTM_VALUATION_TYPES)


class CTRMValuationContextMixin:
    date = fields.Date("Valuation Date")
    product = fields.Many2One('product.product', "Product")
    counterparty = fields.Many2One('party.party', "Counterparty")
    currency = fields.Many2One('currency.currency', "Currency")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    sale = fields.Many2One('sale.sale', "Sale")
    strategy = fields.Many2One('mtm.strategy', "Strategy")
    state = fields.Char("State")

    @classmethod
    def default_date(cls):
        Date = Pool().get('ir.date')
        return Date.today()


class CTRMPhysicalPositionContext(
        CTRMValuationContextMixin, ModelView):
    "CTRM Physical Position Context"
    __name__ = 'ctrm.reporting.position.physical.context'


class CTRMPhysicalPosition(ModelSQL, ModelView):
    "CTRM Physical Position"
    __name__ = 'ctrm.reporting.position.physical'

    valuation_date = fields.Date("Valuation Date")
    lot = fields.Many2One('lot.lot', "Lot")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    purchase_line = fields.Many2One('purchase.line', "Purchase Line")
    sale = fields.Many2One('sale.sale', "Sale")
    sale_line = fields.Many2One('sale.line', "Sale Line")
    type = fields.Selection([
            ('priced', 'Price'),
            ('pur. priced', 'Pur. price'),
            ('pur. efp', 'Pur. efp'),
            ('sale priced', 'Sale price'),
            ('sale efp', 'Sale efp'),
            ('line fee', 'Line fee'),
            ('pur. fee', 'Pur. fee'),
            ('sale fee', 'Sale fee'),
            ('shipment fee', 'Shipment fee'),
            ('market', 'Market'),
            ], "Type")
    reference = fields.Char("Reference")
    counterparty = fields.Many2One('party.party', "Counterparty")
    product = fields.Many2One('product.product', "Product")
    state = fields.Char("State")
    price = fields.Numeric("Price", digits=(16, 4))
    currency = fields.Many2One('currency.currency', "Currency")
    quantity = fields.Numeric("Quantity", digits=(16, 5))
    unit = fields.Many2One('product.uom', "Unit")
    amount = fields.Numeric("Amount", digits=(16, 2))
    base_amount = fields.Numeric("Base Amount", digits=(16, 2))
    mtm_price = fields.Numeric("MTM Price", digits=(16, 4))
    mtm = fields.Numeric("MTM", digits=(16, 2))
    strategy = fields.Many2One('mtm.strategy', "Strategy")

    @classmethod
    def table_query(cls):
        ValuationLine = Pool().get('valuation.valuation.line')
        val = ValuationLine.__table__()

        context = Transaction().context
        where = val.type.in_(PHYSICAL_VALUATION_TYPES)
        if context.get('date'):
            where &= val.date == context['date']
        if context.get('product'):
            where &= val.product == context['product']
        if context.get('counterparty'):
            where &= val.counterparty == context['counterparty']
        if context.get('currency'):
            where &= val.currency == context['currency']
        if context.get('purchase'):
            where &= val.purchase == context['purchase']
        if context.get('sale'):
            where &= val.sale == context['sale']
        if context.get('strategy'):
            where &= val.strategy == context['strategy']
        if context.get('state'):
            where &= val.state == context['state']

        return val.select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            val.id.as_('id'),
            val.date.as_('valuation_date'),
            val.lot.as_('lot'),
            val.purchase.as_('purchase'),
            val.line.as_('purchase_line'),
            val.sale.as_('sale'),
            val.sale_line.as_('sale_line'),
            val.type.as_('type'),
            val.reference.as_('reference'),
            val.counterparty.as_('counterparty'),
            val.product.as_('product'),
            val.state.as_('state'),
            val.price.as_('price'),
            val.currency.as_('currency'),
            val.quantity.as_('quantity'),
            val.unit.as_('unit'),
            val.amount.as_('amount'),
            val.base_amount.as_('base_amount'),
            val.mtm_price.as_('mtm_price'),
            val.mtm.as_('mtm'),
            val.strategy.as_('strategy'),
            where=where)


class CTRMFinancialPositionContext(
        CTRMValuationContextMixin, ModelView):
    "CTRM Financial Position Context"
    __name__ = 'ctrm.reporting.position.financial.context'


class CTRMFinancialPosition(ModelSQL, ModelView):
    "CTRM Financial Position"
    __name__ = 'ctrm.reporting.position.financial'

    valuation_date = fields.Date("Valuation Date")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    purchase_line = fields.Many2One('purchase.line', "Purchase Line")
    sale = fields.Many2One('sale.sale', "Sale")
    sale_line = fields.Many2One('sale.line', "Sale Line")
    reference = fields.Char("Reference")
    counterparty = fields.Many2One('party.party', "Counterparty")
    product = fields.Many2One('product.product', "Product")
    state = fields.Char("State")
    price = fields.Numeric("Price", digits=(16, 4))
    currency = fields.Many2One('currency.currency', "Currency")
    quantity = fields.Numeric("Quantity", digits=(16, 5))
    unit = fields.Many2One('product.uom', "Unit")
    amount = fields.Numeric("Amount", digits=(16, 2))
    base_amount = fields.Numeric("Base Amount", digits=(16, 2))
    mtm_price = fields.Numeric("MTM Price", digits=(16, 4))
    mtm = fields.Numeric("MTM", digits=(16, 2))
    strategy = fields.Many2One('mtm.strategy', "Strategy")

    @classmethod
    def table_query(cls):
        ValuationLine = Pool().get('valuation.valuation.line')
        val = ValuationLine.__table__()

        context = Transaction().context
        where = val.type.in_(DERIVATIVE_VALUATION_TYPES)
        if context.get('date'):
            where &= val.date == context['date']
        if context.get('product'):
            where &= val.product == context['product']
        if context.get('counterparty'):
            where &= val.counterparty == context['counterparty']
        if context.get('currency'):
            where &= val.currency == context['currency']
        if context.get('purchase'):
            where &= val.purchase == context['purchase']
        if context.get('sale'):
            where &= val.sale == context['sale']
        if context.get('strategy'):
            where &= val.strategy == context['strategy']
        if context.get('state'):
            where &= val.state == context['state']

        return val.select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            val.id.as_('id'),
            val.date.as_('valuation_date'),
            val.purchase.as_('purchase'),
            val.line.as_('purchase_line'),
            val.sale.as_('sale'),
            val.sale_line.as_('sale_line'),
            val.reference.as_('reference'),
            val.counterparty.as_('counterparty'),
            val.product.as_('product'),
            val.state.as_('state'),
            val.price.as_('price'),
            val.currency.as_('currency'),
            val.quantity.as_('quantity'),
            val.unit.as_('unit'),
            val.amount.as_('amount'),
            val.base_amount.as_('base_amount'),
            val.mtm_price.as_('mtm_price'),
            val.mtm.as_('mtm'),
            val.strategy.as_('strategy'),
            where=where)


class CTRMNetPositionContext(
        CTRMValuationContextMixin, ModelView):
    "CTRM Net Position Context"
    __name__ = 'ctrm.reporting.position.net.context'


class CTRMNetPosition(ModelSQL, ModelView):
    "CTRM Net Position"
    __name__ = 'ctrm.reporting.position.net'

    valuation_date = fields.Date("Valuation Date")
    product = fields.Many2One('product.product', "Product")
    counterparty = fields.Many2One('party.party', "Counterparty")
    currency = fields.Many2One('currency.currency', "Currency")
    unit = fields.Many2One('product.uom', "Unit")
    state = fields.Char("State")
    strategy = fields.Many2One('mtm.strategy', "Strategy")
    physical_quantity = fields.Numeric(
        "Physical Quantity", digits=(16, 5))
    derivative_quantity = fields.Numeric(
        "Derivative Quantity", digits=(16, 5))
    net_quantity = fields.Numeric("Net Quantity", digits=(16, 5))
    physical_amount = fields.Numeric("Physical Amount", digits=(16, 2))
    derivative_amount = fields.Numeric("Derivative Amount", digits=(16, 2))
    net_amount = fields.Numeric("Net Amount", digits=(16, 2))
    mtm = fields.Numeric("MTM", digits=(16, 2))

    @classmethod
    def table_query(cls):
        ValuationLine = Pool().get('valuation.valuation.line')
        val = ValuationLine.__table__()

        context = Transaction().context
        where = val.type.in_(
            PHYSICAL_VALUATION_TYPES + DERIVATIVE_VALUATION_TYPES
            + MTM_VALUATION_TYPES)
        if context.get('date'):
            where &= val.date == context['date']
        if context.get('product'):
            where &= val.product == context['product']
        if context.get('counterparty'):
            where &= val.counterparty == context['counterparty']
        if context.get('currency'):
            where &= val.currency == context['currency']
        if context.get('purchase'):
            where &= val.purchase == context['purchase']
        if context.get('sale'):
            where &= val.sale == context['sale']
        if context.get('strategy'):
            where &= val.strategy == context['strategy']
        if context.get('state'):
            where &= val.state == context['state']

        is_derivative = val.type.in_(DERIVATIVE_VALUATION_TYPES)
        is_mtm = val.type.in_(MTM_VALUATION_TYPES)
        physical_quantity = Case(
            (is_derivative | is_mtm, 0), else_=Coalesce(val.quantity, 0))
        derivative_quantity = Case(
            (is_derivative, Coalesce(val.quantity, 0)), else_=0)
        physical_amount = Case(
            (is_derivative | is_mtm, 0), else_=Coalesce(val.amount, 0))
        derivative_amount = Case(
            (is_derivative, Coalesce(val.amount, 0)), else_=0)
        net_quantity = Case(
            (is_mtm, 0), else_=Coalesce(val.quantity, 0))

        group_by = [
            val.date,
            val.product,
            val.counterparty,
            val.currency,
            val.unit,
            val.state,
            val.strategy,
            ]

        return val.select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            Min(val.id).as_('id'),
            val.date.as_('valuation_date'),
            val.product.as_('product'),
            val.counterparty.as_('counterparty'),
            val.currency.as_('currency'),
            val.unit.as_('unit'),
            val.state.as_('state'),
            val.strategy.as_('strategy'),
            Sum(physical_quantity).as_('physical_quantity'),
            Sum(derivative_quantity).as_('derivative_quantity'),
            Sum(net_quantity).as_('net_quantity'),
            Sum(physical_amount).as_('physical_amount'),
            Sum(derivative_amount).as_('derivative_amount'),
            Sum(Coalesce(val.amount, 0)).as_('net_amount'),
            Sum(Coalesce(val.mtm, 0)).as_('mtm'),
            where=where,
            group_by=group_by)


class CTRMPnlContextMixin:
    date = fields.Date("Valuation Date")
    product = fields.Many2One('product.product', "Product")
    counterparty = fields.Many2One('party.party', "Counterparty")
    currency = fields.Many2One('currency.currency', "Currency")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    sale = fields.Many2One('sale.sale', "Sale")
    strategy = fields.Many2One('mtm.strategy', "Strategy")
    state = fields.Char("State")

    @classmethod
    def default_date(cls):
        Date = Pool().get('ir.date')
        return Date.today()


class CTRMRealizedPnlContext(CTRMPnlContextMixin, ModelView):
    "CTRM Realized P&L Context"
    __name__ = 'ctrm.reporting.pnl.realized.context'

    realization = fields.Selection([
            ('purchase_and_sale_invoiced', 'Purchase and Sale Invoiced'),
            ('sale_invoiced', 'Sale Invoiced'),
            ('purchase_invoiced', 'Purchase Invoiced'),
            ('all_valued', 'All Valued Lines'),
            ], "Realization")

    @classmethod
    def default_realization(cls):
        return 'purchase_and_sale_invoiced'


class CTRMRealizedPnl(ModelSQL, ModelView):
    "CTRM Realized P&L"
    __name__ = 'ctrm.reporting.pnl.realized'

    valuation_date = fields.Date("Valuation Date")
    lot = fields.Many2One('lot.lot', "Lot")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    purchase_line = fields.Many2One('purchase.line', "Purchase Line")
    purchase_invoice = fields.Many2One('account.invoice', "Purchase Invoice")
    sale = fields.Many2One('sale.sale', "Sale")
    sale_line = fields.Many2One('sale.line', "Sale Line")
    sale_invoice = fields.Many2One('account.invoice', "Sale Invoice")
    type = fields.Selection([
            ('priced', 'Price'),
            ('pur. priced', 'Pur. price'),
            ('pur. efp', 'Pur. efp'),
            ('sale priced', 'Sale price'),
            ('sale efp', 'Sale efp'),
            ('line fee', 'Line fee'),
            ('pur. fee', 'Pur. fee'),
            ('sale fee', 'Sale fee'),
            ('shipment fee', 'Shipment fee'),
            ('market', 'Market'),
            ('derivative', 'Derivative'),
            ], "Type")
    reference = fields.Char("Reference")
    counterparty = fields.Many2One('party.party', "Counterparty")
    product = fields.Many2One('product.product', "Product")
    state = fields.Char("State")
    quantity = fields.Numeric("Quantity", digits=(16, 5))
    unit = fields.Many2One('product.uom', "Unit")
    currency = fields.Many2One('currency.currency', "Currency")
    amount = fields.Numeric("Amount", digits=(16, 2))
    base_amount = fields.Numeric("Base Amount", digits=(16, 2))
    strategy = fields.Many2One('mtm.strategy', "Strategy")

    @classmethod
    def table_query(cls):
        ValuationLine = Pool().get('valuation.valuation.line')
        Lot = Pool().get('lot.lot')
        InvoiceLine = Pool().get('account.invoice.line')
        Invoice = Pool().get('account.invoice')

        val = ValuationLine.__table__()
        lot = Lot.__table__()
        purchase_invoice_line = InvoiceLine.__table__()
        purchase_invoice = Invoice.__table__()
        sale_invoice_line = InvoiceLine.__table__()
        sale_invoice = Invoice.__table__()

        context = Transaction().context
        realization = context.get(
            'realization', 'purchase_and_sale_invoiced')
        where = val.type.in_(
            PHYSICAL_VALUATION_TYPES + DERIVATIVE_VALUATION_TYPES)
        if context.get('date'):
            where &= val.date == context['date']
        if context.get('product'):
            where &= val.product == context['product']
        if context.get('counterparty'):
            where &= val.counterparty == context['counterparty']
        if context.get('currency'):
            where &= val.currency == context['currency']
        if context.get('purchase'):
            where &= val.purchase == context['purchase']
        if context.get('sale'):
            where &= val.sale == context['sale']
        if context.get('strategy'):
            where &= val.strategy == context['strategy']
        if context.get('state'):
            where &= val.state == context['state']

        if realization == 'purchase_and_sale_invoiced':
            where &= lot.invoice_line != Null
            where &= lot.sale_invoice_line != Null
        elif realization == 'sale_invoiced':
            where &= lot.sale_invoice_line != Null
        elif realization == 'purchase_invoiced':
            where &= lot.invoice_line != Null

        return (
            val
            .join(lot, 'LEFT', condition=lot.id == val.lot)
            .join(purchase_invoice_line, 'LEFT',
                condition=purchase_invoice_line.id == lot.invoice_line)
            .join(purchase_invoice, 'LEFT',
                condition=purchase_invoice.id == purchase_invoice_line.invoice)
            .join(sale_invoice_line, 'LEFT',
                condition=sale_invoice_line.id == lot.sale_invoice_line)
            .join(sale_invoice, 'LEFT',
                condition=sale_invoice.id == sale_invoice_line.invoice)
            .select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                val.id.as_('id'),
                val.date.as_('valuation_date'),
                val.lot.as_('lot'),
                val.purchase.as_('purchase'),
                val.line.as_('purchase_line'),
                purchase_invoice.id.as_('purchase_invoice'),
                val.sale.as_('sale'),
                val.sale_line.as_('sale_line'),
                sale_invoice.id.as_('sale_invoice'),
                val.type.as_('type'),
                val.reference.as_('reference'),
                val.counterparty.as_('counterparty'),
                val.product.as_('product'),
                val.state.as_('state'),
                val.quantity.as_('quantity'),
                val.unit.as_('unit'),
                val.currency.as_('currency'),
                val.amount.as_('amount'),
                val.base_amount.as_('base_amount'),
                val.strategy.as_('strategy'),
                where=where))


class CTRMMtmPnlContext(CTRMPnlContextMixin, ModelView):
    "CTRM Mark-to-Market Context"
    __name__ = 'ctrm.reporting.pnl.mtm.context'


class CTRMMtmPnl(ModelSQL, ModelView):
    "CTRM Mark-to-Market"
    __name__ = 'ctrm.reporting.pnl.mtm'

    valuation_date = fields.Date("Valuation Date")
    lot = fields.Many2One('lot.lot', "Lot")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    purchase_line = fields.Many2One('purchase.line', "Purchase Line")
    sale = fields.Many2One('sale.sale', "Sale")
    sale_line = fields.Many2One('sale.line', "Sale Line")
    type = fields.Selection([
            ('priced', 'Price'),
            ('pur. priced', 'Pur. price'),
            ('pur. efp', 'Pur. efp'),
            ('sale priced', 'Sale price'),
            ('sale efp', 'Sale efp'),
            ('line fee', 'Line fee'),
            ('pur. fee', 'Pur. fee'),
            ('sale fee', 'Sale fee'),
            ('shipment fee', 'Shipment fee'),
            ('market', 'Market'),
            ('mtm', 'Mtm'),
            ('pur. mtm', 'Pur. Mtm'),
            ('sale mtm', 'Sale Mtm'),
            ('derivative', 'Derivative'),
            ], "Type")
    reference = fields.Char("Reference")
    counterparty = fields.Many2One('party.party', "Counterparty")
    product = fields.Many2One('product.product', "Product")
    state = fields.Char("State")
    quantity = fields.Numeric("Quantity", digits=(16, 5))
    unit = fields.Many2One('product.uom', "Unit")
    price = fields.Numeric("Price", digits=(16, 4))
    mtm_price = fields.Numeric("MTM Price", digits=(16, 4))
    currency = fields.Many2One('currency.currency', "Currency")
    amount = fields.Numeric("Amount", digits=(16, 2))
    mtm = fields.Numeric("MTM", digits=(16, 2))
    mtm_delta = fields.Numeric("MTM Delta", digits=(16, 2))
    strategy = fields.Many2One('mtm.strategy', "Strategy")

    @classmethod
    def table_query(cls):
        ValuationLine = Pool().get('valuation.valuation.line')
        val = ValuationLine.__table__()

        context = Transaction().context
        where = val.type.in_(ALL_VALUATION_TYPES)
        if context.get('date'):
            where &= val.date == context['date']
        if context.get('product'):
            where &= val.product == context['product']
        if context.get('counterparty'):
            where &= val.counterparty == context['counterparty']
        if context.get('currency'):
            where &= val.currency == context['currency']
        if context.get('purchase'):
            where &= val.purchase == context['purchase']
        if context.get('sale'):
            where &= val.sale == context['sale']
        if context.get('strategy'):
            where &= val.strategy == context['strategy']
        if context.get('state'):
            where &= val.state == context['state']

        return val.select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            val.id.as_('id'),
            val.date.as_('valuation_date'),
            val.lot.as_('lot'),
            val.purchase.as_('purchase'),
            val.line.as_('purchase_line'),
            val.sale.as_('sale'),
            val.sale_line.as_('sale_line'),
            val.type.as_('type'),
            val.reference.as_('reference'),
            val.counterparty.as_('counterparty'),
            val.product.as_('product'),
            val.state.as_('state'),
            val.quantity.as_('quantity'),
            val.unit.as_('unit'),
            val.price.as_('price'),
            val.mtm_price.as_('mtm_price'),
            val.currency.as_('currency'),
            val.amount.as_('amount'),
            val.mtm.as_('mtm'),
            (Coalesce(val.mtm, 0) - Coalesce(val.amount, 0)).as_(
                'mtm_delta'),
            val.strategy.as_('strategy'),
            where=where)


class CTRMPnlExplainContext(CTRMPnlContextMixin, ModelView):
    "CTRM P&L Explain Context"
    __name__ = 'ctrm.reporting.pnl.explain.context'


class CTRMPnlExplain(ModelSQL, ModelView):
    "CTRM P&L Explain"
    __name__ = 'ctrm.reporting.pnl.explain'

    valuation_date = fields.Date("Valuation Date")
    product = fields.Many2One('product.product', "Product")
    counterparty = fields.Many2One('party.party', "Counterparty")
    currency = fields.Many2One('currency.currency', "Currency")
    unit = fields.Many2One('product.uom', "Unit")
    state = fields.Char("State")
    strategy = fields.Many2One('mtm.strategy', "Strategy")
    physical_pnl = fields.Numeric("Physical P&L", digits=(16, 2))
    fee_pnl = fields.Numeric("Fee P&L", digits=(16, 2))
    derivative_pnl = fields.Numeric("Derivative P&L", digits=(16, 2))
    mtm_delta = fields.Numeric("MTM Delta", digits=(16, 2))
    total_pnl = fields.Numeric("Total P&L", digits=(16, 2))

    @classmethod
    def table_query(cls):
        ValuationLine = Pool().get('valuation.valuation.line')
        val = ValuationLine.__table__()

        context = Transaction().context
        where = val.type.in_(ALL_VALUATION_TYPES)
        if context.get('date'):
            where &= val.date == context['date']
        if context.get('product'):
            where &= val.product == context['product']
        if context.get('counterparty'):
            where &= val.counterparty == context['counterparty']
        if context.get('currency'):
            where &= val.currency == context['currency']
        if context.get('purchase'):
            where &= val.purchase == context['purchase']
        if context.get('sale'):
            where &= val.sale == context['sale']
        if context.get('strategy'):
            where &= val.strategy == context['strategy']
        if context.get('state'):
            where &= val.state == context['state']

        is_fee = val.type.in_([
                'line fee',
                'pur. fee',
                'sale fee',
                'shipment fee',
                ])
        is_derivative = val.type.in_(DERIVATIVE_VALUATION_TYPES)
        is_mtm = val.type.in_(MTM_VALUATION_TYPES)
        physical_pnl = Case(
            (is_fee | is_derivative | is_mtm, 0),
            else_=Coalesce(val.amount, 0))
        fee_pnl = Case((is_fee, Coalesce(val.amount, 0)), else_=0)
        derivative_pnl = Case(
            (is_derivative, Coalesce(val.amount, 0)), else_=0)
        mtm_delta = Coalesce(val.mtm, 0) - Coalesce(val.amount, 0)

        group_by = [
            val.date,
            val.product,
            val.counterparty,
            val.currency,
            val.unit,
            val.state,
            val.strategy,
            ]

        return val.select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            Min(val.id).as_('id'),
            val.date.as_('valuation_date'),
            val.product.as_('product'),
            val.counterparty.as_('counterparty'),
            val.currency.as_('currency'),
            val.unit.as_('unit'),
            val.state.as_('state'),
            val.strategy.as_('strategy'),
            Sum(physical_pnl).as_('physical_pnl'),
            Sum(fee_pnl).as_('fee_pnl'),
            Sum(derivative_pnl).as_('derivative_pnl'),
            Sum(mtm_delta).as_('mtm_delta'),
            Sum(Coalesce(val.amount, 0)).as_('total_pnl'),
            where=where,
            group_by=group_by)


class CTRMPnlDimensionContext(CTRMPnlContextMixin, ModelView):
    "CTRM P&L by Dimension Context"
    __name__ = 'ctrm.reporting.pnl.dimension.context'

    dimension = fields.Many2One('analytic.dimension', "Dimension")
    value = fields.Many2One('analytic.dimension.value', "Value")


class CTRMPnlDimension(ModelSQL, ModelView):
    "CTRM P&L by Dimension"
    __name__ = 'ctrm.reporting.pnl.dimension'

    valuation_date = fields.Date("Valuation Date")
    dimension = fields.Many2One('analytic.dimension', "Dimension")
    value = fields.Many2One('analytic.dimension.value', "Value")
    product = fields.Many2One('product.product', "Product")
    counterparty = fields.Many2One('party.party', "Counterparty")
    currency = fields.Many2One('currency.currency', "Currency")
    unit = fields.Many2One('product.uom', "Unit")
    state = fields.Char("State")
    strategy = fields.Many2One('mtm.strategy', "Strategy")
    quantity = fields.Numeric("Quantity", digits=(16, 5))
    amount = fields.Numeric("Amount", digits=(16, 2))
    mtm = fields.Numeric("MTM", digits=(16, 2))
    mtm_delta = fields.Numeric("MTM Delta", digits=(16, 2))

    @classmethod
    def table_query(cls):
        ValuationLine = Pool().get('valuation.valuation.line')
        Assignment = Pool().get('analytic.dimension.assignment')
        val = ValuationLine.__table__()
        purchase_assignment = Assignment.__table__()
        sale_assignment = Assignment.__table__()

        context = Transaction().context
        dimension_expr = Coalesce(
            sale_assignment.dimension, purchase_assignment.dimension)
        value_expr = Coalesce(sale_assignment.value, purchase_assignment.value)

        where = val.type.in_(ALL_VALUATION_TYPES)
        if context.get('date'):
            where &= val.date == context['date']
        if context.get('product'):
            where &= val.product == context['product']
        if context.get('counterparty'):
            where &= val.counterparty == context['counterparty']
        if context.get('currency'):
            where &= val.currency == context['currency']
        if context.get('purchase'):
            where &= val.purchase == context['purchase']
        if context.get('sale'):
            where &= val.sale == context['sale']
        if context.get('strategy'):
            where &= val.strategy == context['strategy']
        if context.get('state'):
            where &= val.state == context['state']
        if context.get('dimension'):
            where &= dimension_expr == context['dimension']
        if context.get('value'):
            where &= value_expr == context['value']

        group_by = [
            val.date,
            dimension_expr,
            value_expr,
            val.product,
            val.counterparty,
            val.currency,
            val.unit,
            val.state,
            val.strategy,
            ]
        grouped = (
            val
            .join(purchase_assignment, 'LEFT',
                condition=purchase_assignment.purchase == val.purchase)
            .join(sale_assignment, 'LEFT',
                condition=sale_assignment.sale == val.sale)
            .select(
                val.date.as_('valuation_date'),
                dimension_expr.as_('dimension'),
                value_expr.as_('value'),
                val.product.as_('product'),
                val.counterparty.as_('counterparty'),
                val.currency.as_('currency'),
                val.unit.as_('unit'),
                val.state.as_('state'),
                val.strategy.as_('strategy'),
                Sum(Case(
                    (val.type.in_(MTM_VALUATION_TYPES), 0),
                    else_=Coalesce(val.quantity, 0))).as_('quantity'),
                Sum(Coalesce(val.amount, 0)).as_('amount'),
                Sum(Coalesce(val.mtm, 0)).as_('mtm'),
                Sum(Coalesce(val.mtm, 0) - Coalesce(val.amount, 0)).as_(
                    'mtm_delta'),
                where=where,
                group_by=group_by))

        row_number = RowNumber(window=Window([],
                order_by=[
                    grouped.valuation_date.asc,
                    grouped.dimension.asc,
                    grouped.value.asc,
                    grouped.product.asc,
                    grouped.counterparty.asc,
                    grouped.currency.asc,
                    grouped.unit.asc,
                    grouped.state.asc,
                    grouped.strategy.asc,
                    ]))

        return grouped.select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            row_number.as_('id'),
            grouped.valuation_date.as_('valuation_date'),
            grouped.dimension.as_('dimension'),
            grouped.value.as_('value'),
            grouped.product.as_('product'),
            grouped.counterparty.as_('counterparty'),
            grouped.currency.as_('currency'),
            grouped.unit.as_('unit'),
            grouped.state.as_('state'),
            grouped.strategy.as_('strategy'),
            grouped.quantity.as_('quantity'),
            grouped.amount.as_('amount'),
            grouped.mtm.as_('mtm'),
            grouped.mtm_delta.as_('mtm_delta'))


class CTRMCreditRiskContext(ModelView):
    "CTRM Credit Risk Context"
    __name__ = 'ctrm.reporting.risk.credit.context'

    party = fields.Many2One('party.party', "Party")
    currency = fields.Many2One('currency.currency', "Credit Currency")
    risk_level = fields.Selection([
            (None, ''),
            ('low', 'Low'),
            ('medium', 'Medium'),
            ('high', 'High'),
            ], "Risk Level")
    over_limit_only = fields.Boolean("Over Limit Only")


class CTRMCreditRisk(ModelSQL, ModelView):
    "CTRM Credit Risk"
    __name__ = 'ctrm.reporting.risk.credit'

    party = fields.Many2One('party.party', "Party")
    credit_limit = fields.Numeric("Credit Limit", digits=(16, 2))
    credit_currency = fields.Many2One(
        'currency.currency', "Credit Currency")
    open_exposure = fields.Numeric("Open Exposure", digits=(16, 2))
    internal_limit = fields.Numeric("Internal Limit", digits=(16, 2))
    insurance_limit = fields.Numeric("Insurance Limit", digits=(16, 2))
    available_credit = fields.Numeric("Available Credit", digits=(16, 2))
    utilization = fields.Numeric("Utilization %", digits=(16, 2))
    risk_level = fields.Selection([
            (None, ''),
            ('low', 'Low'),
            ('medium', 'Medium'),
            ('high', 'High'),
            ], "Risk Level")
    status = fields.Char("Status")

    @classmethod
    def table_query(cls):
        Party = Pool().get('party.party')
        MoveLine = Pool().get('account.move.line')
        InternalLimit = Pool().get('party.internal.limit')
        InsuranceLimit = Pool().get('party.insurance.limit')

        party = Party.__table__()
        move_line = MoveLine.__table__()
        internal_limit = InternalLimit.__table__()
        insurance_limit = InsuranceLimit.__table__()

        exposure_query = move_line.select(
            move_line.party.as_('party'),
            Sum(Coalesce(move_line.debit, 0)
                - Coalesce(move_line.credit, 0)).as_('open_exposure'),
            where=((move_line.party != Null)
                & (move_line.reconciliation == Null)),
            group_by=[move_line.party])
        internal_query = internal_limit.select(
            internal_limit.party.as_('party'),
            Sum(Coalesce(internal_limit.amount, 0)).as_('internal_limit'),
            group_by=[internal_limit.party])
        insurance_query = insurance_limit.select(
            insurance_limit.party.as_('party'),
            Sum(Coalesce(insurance_limit.amount, 0)).as_('insurance_limit'),
            group_by=[insurance_limit.party])

        exposure = Coalesce(exposure_query.open_exposure, 0)
        limit = Coalesce(party.credit_limit, 0)
        utilization = Case(
            (limit != 0, (exposure / limit) * 100),
            else_=0)
        available_credit = limit - exposure
        status = Case(
            (exposure > limit, 'Over limit'),
            (exposure > limit * 0.8, 'Warning'),
            else_='OK')

        context = Transaction().context
        where = (
            (party.credit_limit != Null)
            | (exposure_query.open_exposure != Null)
            | (internal_query.internal_limit != Null)
            | (insurance_query.insurance_limit != Null))
        if context.get('party'):
            where &= party.id == context['party']
        if context.get('currency'):
            where &= party.credit_currency == context['currency']
        if context.get('risk_level'):
            where &= party.risk_level == context['risk_level']
        if context.get('over_limit_only'):
            where &= exposure > limit

        return (
            party
            .join(exposure_query, 'LEFT',
                condition=exposure_query.party == party.id)
            .join(internal_query, 'LEFT',
                condition=internal_query.party == party.id)
            .join(insurance_query, 'LEFT',
                condition=insurance_query.party == party.id)
            .select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                party.id.as_('id'),
                party.id.as_('party'),
                party.credit_limit.as_('credit_limit'),
                party.credit_currency.as_('credit_currency'),
                exposure.as_('open_exposure'),
                Coalesce(internal_query.internal_limit, 0).as_(
                    'internal_limit'),
                Coalesce(insurance_query.insurance_limit, 0).as_(
                    'insurance_limit'),
                available_credit.as_('available_credit'),
                utilization.as_('utilization'),
                party.risk_level.as_('risk_level'),
                status.as_('status'),
                where=where))


class CTRMShippingLogisticsContext(ModelView):
    "CTRM Shipping Logistics Context"
    __name__ = 'ctrm.reporting.operations.shipping.context'

    from_date = fields.Date("From Date")
    to_date = fields.Date("To Date")
    supplier = fields.Many2One('party.party', "Supplier")
    warehouse = fields.Many2One('stock.location', "Warehouse")
    transport_type = fields.Selection([
            (None, ''),
            ('vessel', 'Vessel'),
            ('truck', 'Truck'),
            ('other', 'Other'),
            ], "Transport Type")
    vessel = fields.Many2One('trade.vessel', "Vessel")
    state = fields.Char("State")


class CTRMShippingLogistics(ModelSQL, ModelView):
    "CTRM Shipping Logistics"
    __name__ = 'ctrm.reporting.operations.shipping'

    shipment = fields.Many2One('stock.shipment.in', "Shipment")
    reference = fields.Char("Reference")
    supplier = fields.Many2One('party.party', "Supplier")
    warehouse = fields.Many2One('stock.location', "Warehouse")
    from_location = fields.Many2One('stock.location', "From")
    to_location = fields.Many2One('stock.location', "To")
    transport_type = fields.Selection([
            (None, ''),
            ('vessel', 'Vessel'),
            ('truck', 'Truck'),
            ('other', 'Other'),
            ], "Transport Type")
    vessel = fields.Many2One('trade.vessel', "Vessel")
    state = fields.Char("State")
    planned_date = fields.Date("Planned Date")
    effective_date = fields.Date("Effective Date")
    estimated_loading = fields.Date("Estimated Loading")
    eta_pol = fields.Date("ETA POL")
    eta = fields.Date("ETA")
    etd = fields.Date("ETD")
    discharged_date = fields.Date("Discharged Date")
    booking = fields.Char("Booking")
    bl_number = fields.Char("BL Number")
    product = fields.Many2One('product.product', "Product")
    quantity = fields.Numeric("Quantity", digits=(16, 5))
    unit = fields.Many2One('product.uom', "Unit")
    lot_count = fields.Integer("Lots")

    @classmethod
    def table_query(cls):
        ShipmentIn = Pool().get('stock.shipment.in')
        Lot = Pool().get('lot.lot')

        shipment = ShipmentIn.__table__()
        lot = Lot.__table__()

        lot_query = lot.select(
            lot.lot_shipment_in.as_('shipment'),
            Count(lot.id).as_('lot_count'),
            Sum(Coalesce(lot.lot_qt, 0)).as_('quantity'),
            Max(lot.lot_product).as_('product'),
            Max(lot.lot_unit_line).as_('unit'),
            where=((lot.lot_shipment_in != Null)
                & (lot.lot_type == 'physic')),
            group_by=[lot.lot_shipment_in])

        context = Transaction().context
        logistics_date = Coalesce(
            shipment.etad, shipment.eta, shipment.planned_date)
        where = shipment.id != Null
        if context.get('from_date'):
            where &= logistics_date >= context['from_date']
        if context.get('to_date'):
            where &= logistics_date <= context['to_date']
        if context.get('supplier'):
            where &= shipment.supplier == context['supplier']
        if context.get('warehouse'):
            where &= shipment.warehouse == context['warehouse']
        if context.get('transport_type'):
            where &= shipment.transport_type == context['transport_type']
        if context.get('vessel'):
            where &= shipment.vessel == context['vessel']
        if context.get('state'):
            where &= shipment.state == context['state']

        return (
            shipment
            .join(lot_query, 'LEFT',
                condition=lot_query.shipment == shipment.id)
            .select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                shipment.id.as_('id'),
                shipment.id.as_('shipment'),
                shipment.ref.as_('reference'),
                shipment.supplier.as_('supplier'),
                shipment.warehouse.as_('warehouse'),
                shipment.from_location.as_('from_location'),
                shipment.to_location.as_('to_location'),
                shipment.transport_type.as_('transport_type'),
                shipment.vessel.as_('vessel'),
                shipment.state.as_('state'),
                shipment.planned_date.as_('planned_date'),
                shipment.effective_date.as_('effective_date'),
                shipment.etl.as_('estimated_loading'),
                shipment.eta.as_('eta_pol'),
                shipment.etad.as_('eta'),
                shipment.etd.as_('etd'),
                shipment.unloaded.as_('discharged_date'),
                shipment.booking.as_('booking'),
                shipment.bl_number.as_('bl_number'),
                lot_query.product.as_('product'),
                Coalesce(lot_query.quantity, 0).as_('quantity'),
                lot_query.unit.as_('unit'),
                Coalesce(lot_query.lot_count, 0).as_('lot_count'),
                where=where))


class CTRMInventoryContext(ModelView):
    "CTRM Inventory Context"
    __name__ = 'ctrm.reporting.operations.inventory.context'

    product = fields.Many2One('product.product', "Product")
    supplier = fields.Many2One('party.party', "Supplier")
    client = fields.Many2One('party.party', "Client")
    warehouse = fields.Many2One('stock.location', "Warehouse")
    lot_status = fields.Selection([
            (None, ''),
            ('forecast', 'Forecast'),
            ('loading', 'Loading'),
            ('transit', 'Transit'),
            ('destination', 'Destination'),
            ('stock', 'Stock'),
            ('delivered', 'Delivered'),
            ], "Status")
    availability = fields.Selection([
            (None, ''),
            ('available', 'Available'),
            ('reserved', 'Reserved'),
            ('locked', 'Locked'),
            ('prov', 'Prov. inv'),
            ('invoiced', 'Invoiced'),
            ], "Availability")


class CTRMInventory(ModelSQL, ModelView):
    "CTRM Inventory"
    __name__ = 'ctrm.reporting.operations.inventory'

    lot = fields.Many2One('lot.lot', "Lot")
    product = fields.Many2One('product.product', "Product")
    supplier = fields.Many2One('party.party', "Supplier")
    client = fields.Many2One('party.party', "Client")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    purchase_line = fields.Many2One('purchase.line', "Purchase Line")
    sale = fields.Many2One('sale.sale', "Sale")
    sale_line = fields.Many2One('sale.line', "Sale Line")
    lot_status = fields.Selection([
            (None, ''),
            ('forecast', 'Forecast'),
            ('loading', 'Loading'),
            ('transit', 'Transit'),
            ('destination', 'Destination'),
            ('stock', 'Stock'),
            ('delivered', 'Delivered'),
            ], "Status")
    availability = fields.Selection([
            (None, ''),
            ('available', 'Available'),
            ('reserved', 'Reserved'),
            ('locked', 'Locked'),
            ('prov', 'Prov. inv'),
            ('invoiced', 'Invoiced'),
            ], "Availability")
    warehouse = fields.Many2One('stock.location', "Warehouse")
    from_location = fields.Many2One('stock.location', "From")
    to_location = fields.Many2One('stock.location', "To")
    shipment_in = fields.Many2One('stock.shipment.in', "Shipment In")
    shipment_out = fields.Many2One('stock.shipment.out', "Shipment Out")
    shipment_internal = fields.Many2One(
        'stock.shipment.internal', "Shipment Internal")
    container = fields.Char("Container")
    quantity = fields.Numeric("Quantity", digits=(16, 5))
    unit = fields.Many2One('product.uom', "Unit")

    @classmethod
    def table_query(cls):
        pool = Pool()
        Lot = pool.get('lot.lot')
        PurchaseLine = pool.get('purchase.line')
        Purchase = pool.get('purchase.purchase')
        SaleLine = pool.get('sale.line')
        Sale = pool.get('sale.sale')
        ShipmentIn = pool.get('stock.shipment.in')

        lot = Lot.__table__()
        purchase_line = PurchaseLine.__table__()
        purchase = Purchase.__table__()
        sale_line = SaleLine.__table__()
        sale = Sale.__table__()
        shipment_in = ShipmentIn.__table__()

        context = Transaction().context
        warehouse = shipment_in.warehouse
        from_location = shipment_in.from_location
        to_location = shipment_in.to_location
        where = lot.lot_type == 'physic'
        if context.get('product'):
            where &= lot.lot_product == context['product']
        if context.get('supplier'):
            where &= purchase.party == context['supplier']
        if context.get('client'):
            where &= sale.party == context['client']
        if context.get('warehouse'):
            where &= warehouse == context['warehouse']
        if context.get('lot_status'):
            where &= lot.lot_status == context['lot_status']
        if context.get('availability'):
            where &= lot.lot_av == context['availability']

        return (
            lot
            .join(purchase_line, 'LEFT',
                condition=lot.line == purchase_line.id)
            .join(purchase, 'LEFT',
                condition=purchase_line.purchase == purchase.id)
            .join(sale_line, 'LEFT',
                condition=lot.sale_line == sale_line.id)
            .join(sale, 'LEFT',
                condition=sale_line.sale == sale.id)
            .join(shipment_in, 'LEFT',
                condition=lot.lot_shipment_in == shipment_in.id)
            .select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                lot.id.as_('id'),
                lot.id.as_('lot'),
                lot.lot_product.as_('product'),
                purchase.party.as_('supplier'),
                sale.party.as_('client'),
                purchase.id.as_('purchase'),
                lot.line.as_('purchase_line'),
                sale.id.as_('sale'),
                lot.sale_line.as_('sale_line'),
                lot.lot_status.as_('lot_status'),
                lot.lot_av.as_('availability'),
                warehouse.as_('warehouse'),
                from_location.as_('from_location'),
                to_location.as_('to_location'),
                lot.lot_shipment_in.as_('shipment_in'),
                lot.lot_shipment_out.as_('shipment_out'),
                lot.lot_shipment_internal.as_('shipment_internal'),
                lot.lot_container.as_('container'),
                Coalesce(lot.lot_qt, 0).as_('quantity'),
                lot.lot_unit_line.as_('unit'),
                where=where))


class CTRMContractPerformanceContext(ModelView):
    "CTRM Contract Performance Context"
    __name__ = 'ctrm.reporting.operations.contract.context'

    side = fields.Selection([
            (None, ''),
            ('purchase', 'Purchase'),
            ('sale', 'Sale'),
            ], "Side")
    product = fields.Many2One('product.product', "Product")
    counterparty = fields.Many2One('party.party', "Counterparty")
    delivery_period = fields.Many2One('product.month', "Shipment Period")
    status = fields.Selection([
            (None, ''),
            ('under', 'Under delivery'),
            ('in_tolerance', 'In tolerance'),
            ('over', 'Over delivery'),
            ], "Status")
    open_only = fields.Boolean("Open Only")


class CTRMContractPerformance(ModelSQL, ModelView):
    "CTRM Contract Performance"
    __name__ = 'ctrm.reporting.operations.contract'

    side = fields.Selection([
            ('purchase', 'Purchase'),
            ('sale', 'Sale'),
            ], "Side")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    purchase_line = fields.Many2One('purchase.line', "Purchase Line")
    sale = fields.Many2One('sale.sale', "Sale")
    sale_line = fields.Many2One('sale.line', "Sale Line")
    counterparty = fields.Many2One('party.party', "Counterparty")
    product = fields.Many2One('product.product', "Product")
    delivery_period = fields.Many2One('product.month', "Shipment Period")
    from_date = fields.Date("From")
    to_date = fields.Date("To")
    contract_quantity = fields.Numeric("Contract Quantity", digits=(16, 5))
    actual_quantity = fields.Numeric("Actual Quantity", digits=(16, 5))
    variance_quantity = fields.Numeric("Variance", digits=(16, 5))
    tolerance_min = fields.Numeric("Tolerance Min %", digits=(16, 5))
    tolerance_max = fields.Numeric("Tolerance Max %", digits=(16, 5))
    status = fields.Selection([
            ('under', 'Under delivery'),
            ('in_tolerance', 'In tolerance'),
            ('over', 'Over delivery'),
            ], "Status")
    finished = fields.Boolean("Finished")
    unit = fields.Many2One('product.uom', "Unit")
    lot_count = fields.Integer("Lots")

    @classmethod
    def table_query(cls):
        pool = Pool()
        Lot = pool.get('lot.lot')
        PurchaseLine = pool.get('purchase.line')
        Purchase = pool.get('purchase.purchase')
        SaleLine = pool.get('sale.line')
        Sale = pool.get('sale.sale')

        lot = Lot.__table__()
        purchase_line = PurchaseLine.__table__()
        purchase = Purchase.__table__()
        sale_line = SaleLine.__table__()
        sale = Sale.__table__()

        purchase_contract_quantity = Coalesce(
            purchase_line.quantity_theorical, purchase_line.quantity, 0)
        purchase_tolerance_min = Coalesce(
            purchase_line.tol_min, purchase.tol_min, 0)
        purchase_tolerance_max = Coalesce(
            purchase_line.tol_max, purchase.tol_max, 0)
        purchase_actual_quantity = Sum(Coalesce(lot.lot_qt, 0))
        purchase_min_quantity = purchase_contract_quantity * (
            Literal(1) - (purchase_tolerance_min / Literal(100)))
        purchase_max_quantity = purchase_contract_quantity * (
            Literal(1) + (purchase_tolerance_max / Literal(100)))
        purchase_status = Case(
            (purchase_actual_quantity < purchase_min_quantity, 'under'),
            (purchase_actual_quantity > purchase_max_quantity, 'over'),
            else_='in_tolerance')

        sale_contract_quantity = Coalesce(
            sale_line.quantity_theorical, sale_line.quantity, 0)
        sale_tolerance_min = Coalesce(sale_line.tol_min, sale.tol_min, 0)
        sale_tolerance_max = Coalesce(sale_line.tol_max, sale.tol_max, 0)
        sale_actual_quantity = Sum(Coalesce(lot.lot_qt, 0))
        sale_min_quantity = sale_contract_quantity * (
            Literal(1) - (sale_tolerance_min / Literal(100)))
        sale_max_quantity = sale_contract_quantity * (
            Literal(1) + (sale_tolerance_max / Literal(100)))
        sale_status = Case(
            (sale_actual_quantity < sale_min_quantity, 'under'),
            (sale_actual_quantity > sale_max_quantity, 'over'),
            else_='in_tolerance')

        context = Transaction().context
        purchase_where = (lot.lot_type == 'physic') & (lot.line != Null)
        if context.get('product'):
            purchase_where &= purchase_line.product == context['product']
        if context.get('counterparty'):
            purchase_where &= purchase.party == context['counterparty']
        if context.get('delivery_period'):
            purchase_where &= purchase_line.del_period == (
                context['delivery_period'])
        if context.get('open_only'):
            purchase_where &= purchase_line.finished == False
        purchase_query = (
            lot
            .join(purchase_line, condition=lot.line == purchase_line.id)
            .join(purchase, condition=purchase_line.purchase == purchase.id)
            .select(
                Literal('purchase').as_('side'),
                purchase.id.as_('purchase'),
                lot.line.as_('purchase_line'),
                Literal(None).as_('sale'),
                Literal(None).as_('sale_line'),
                purchase.party.as_('counterparty'),
                purchase_line.product.as_('product'),
                purchase_line.del_period.as_('delivery_period'),
                purchase_line.from_del.as_('from_date'),
                purchase_line.to_del.as_('to_date'),
                purchase_contract_quantity.as_('contract_quantity'),
                purchase_actual_quantity.as_('actual_quantity'),
                (purchase_actual_quantity - purchase_contract_quantity).as_(
                    'variance_quantity'),
                purchase_tolerance_min.as_('tolerance_min'),
                purchase_tolerance_max.as_('tolerance_max'),
                purchase_status.as_('status'),
                purchase_line.finished.as_('finished'),
                purchase_line.unit.as_('unit'),
                Count(lot.id).as_('lot_count'),
                where=purchase_where,
                group_by=[
                    purchase.id,
                    lot.line,
                    purchase.party,
                    purchase_line.product,
                    purchase_line.del_period,
                    purchase_line.from_del,
                    purchase_line.to_del,
                    purchase_contract_quantity,
                    purchase_tolerance_min,
                    purchase_tolerance_max,
                    purchase_line.finished,
                    purchase_line.unit,
                    ]))

        sale_where = (lot.lot_type == 'physic') & (lot.sale_line != Null)
        if context.get('product'):
            sale_where &= sale_line.product == context['product']
        if context.get('counterparty'):
            sale_where &= sale.party == context['counterparty']
        if context.get('delivery_period'):
            sale_where &= sale_line.del_period == context['delivery_period']
        if context.get('open_only'):
            sale_where &= sale_line.finished == False
        sale_query = (
            lot
            .join(sale_line, condition=lot.sale_line == sale_line.id)
            .join(sale, condition=sale_line.sale == sale.id)
            .select(
                Literal('sale').as_('side'),
                Literal(None).as_('purchase'),
                Literal(None).as_('purchase_line'),
                sale.id.as_('sale'),
                lot.sale_line.as_('sale_line'),
                sale.party.as_('counterparty'),
                sale_line.product.as_('product'),
                sale_line.del_period.as_('delivery_period'),
                sale_line.from_del.as_('from_date'),
                sale_line.to_del.as_('to_date'),
                sale_contract_quantity.as_('contract_quantity'),
                sale_actual_quantity.as_('actual_quantity'),
                (sale_actual_quantity - sale_contract_quantity).as_(
                    'variance_quantity'),
                sale_tolerance_min.as_('tolerance_min'),
                sale_tolerance_max.as_('tolerance_max'),
                sale_status.as_('status'),
                sale_line.finished.as_('finished'),
                sale_line.unit.as_('unit'),
                Count(lot.id).as_('lot_count'),
                where=sale_where,
                group_by=[
                    sale.id,
                    lot.sale_line,
                    sale.party,
                    sale_line.product,
                    sale_line.del_period,
                    sale_line.from_del,
                    sale_line.to_del,
                    sale_contract_quantity,
                    sale_tolerance_min,
                    sale_tolerance_max,
                    sale_line.finished,
                    sale_line.unit,
                    ]))

        side = context.get('side')
        if side == 'purchase':
            grouped = Union(purchase_query, purchase_query, all_=False)
        elif side == 'sale':
            grouped = Union(sale_query, sale_query, all_=False)
        else:
            grouped = Union(purchase_query, sale_query, all_=True)

        report_id = RowNumber(window=Window([],
                order_by=[grouped.side, grouped.purchase_line,
                    grouped.sale_line]))
        outer_where = grouped.side != Null
        if context.get('status'):
            outer_where &= grouped.status == context['status']

        return grouped.select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            report_id.as_('id'),
            grouped.side.as_('side'),
            grouped.purchase.as_('purchase'),
            grouped.purchase_line.as_('purchase_line'),
            grouped.sale.as_('sale'),
            grouped.sale_line.as_('sale_line'),
            grouped.counterparty.as_('counterparty'),
            grouped.product.as_('product'),
            grouped.delivery_period.as_('delivery_period'),
            grouped.from_date.as_('from_date'),
            grouped.to_date.as_('to_date'),
            grouped.contract_quantity.as_('contract_quantity'),
            grouped.actual_quantity.as_('actual_quantity'),
            grouped.variance_quantity.as_('variance_quantity'),
            grouped.tolerance_min.as_('tolerance_min'),
            grouped.tolerance_max.as_('tolerance_max'),
            grouped.status.as_('status'),
            grouped.finished.as_('finished'),
            grouped.unit.as_('unit'),
            grouped.lot_count.as_('lot_count'),
            where=outer_where)


class CTRMSchedulingContext(ModelView):
    "CTRM Scheduling Context"
    __name__ = 'ctrm.reporting.operations.scheduling.context'

    from_date = fields.Date("From Date")
    to_date = fields.Date("To Date")
    product = fields.Many2One('product.product', "Product")
    counterparty = fields.Many2One('party.party', "Counterparty")
    warehouse = fields.Many2One('stock.location', "Warehouse")
    shipment_state = fields.Char("Shipment State")
    lot_status = fields.Selection([
            (None, ''),
            ('forecast', 'Forecast'),
            ('loading', 'Loading'),
            ('transit', 'Transit'),
            ('destination', 'Destination'),
            ('stock', 'Stock'),
            ('delivered', 'Delivered'),
            ], "Lot Status")


class CTRMScheduling(ModelSQL, ModelView):
    "CTRM Scheduling"
    __name__ = 'ctrm.reporting.operations.scheduling'

    lot = fields.Many2One('lot.lot', "Lot")
    product = fields.Many2One('product.product', "Product")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    purchase_line = fields.Many2One('purchase.line', "Purchase Line")
    sale = fields.Many2One('sale.sale', "Sale")
    sale_line = fields.Many2One('sale.line', "Sale Line")
    counterparty = fields.Many2One('party.party', "Counterparty")
    shipment_in = fields.Many2One('stock.shipment.in', "Shipment")
    warehouse = fields.Many2One('stock.location', "Warehouse")
    from_location = fields.Many2One('stock.location', "From")
    to_location = fields.Many2One('stock.location', "To")
    delivery_period = fields.Many2One('product.month', "Shipment Period")
    contract_from = fields.Date("Contract From")
    contract_to = fields.Date("Contract To")
    scheduled_date = fields.Date("Scheduled Date")
    eta = fields.Date("ETA")
    etd = fields.Date("ETD")
    actual_date = fields.Date("Actual Date")
    lot_status = fields.Selection([
            (None, ''),
            ('forecast', 'Forecast'),
            ('loading', 'Loading'),
            ('transit', 'Transit'),
            ('destination', 'Destination'),
            ('stock', 'Stock'),
            ('delivered', 'Delivered'),
            ], "Lot Status")
    shipment_state = fields.Char("Shipment State")
    quantity = fields.Numeric("Quantity", digits=(16, 5))
    unit = fields.Many2One('product.uom', "Unit")

    @classmethod
    def table_query(cls):
        pool = Pool()
        Lot = pool.get('lot.lot')
        PurchaseLine = pool.get('purchase.line')
        Purchase = pool.get('purchase.purchase')
        SaleLine = pool.get('sale.line')
        Sale = pool.get('sale.sale')
        ShipmentIn = pool.get('stock.shipment.in')

        lot = Lot.__table__()
        purchase_line = PurchaseLine.__table__()
        purchase = Purchase.__table__()
        sale_line = SaleLine.__table__()
        sale = Sale.__table__()
        shipment = ShipmentIn.__table__()

        counterparty = Case(
            (lot.line != Null, purchase.party), else_=sale.party)
        delivery_period = Coalesce(
            purchase_line.del_period, sale_line.del_period)
        contract_from = Coalesce(purchase_line.from_del, sale_line.from_del)
        contract_to = Coalesce(purchase_line.to_del, sale_line.to_del)
        scheduled_date = Coalesce(
            shipment.etad, shipment.eta, shipment.planned_date,
            contract_from)

        context = Transaction().context
        where = lot.lot_type == 'physic'
        if context.get('from_date'):
            where &= scheduled_date >= context['from_date']
        if context.get('to_date'):
            where &= scheduled_date <= context['to_date']
        if context.get('product'):
            where &= lot.lot_product == context['product']
        if context.get('counterparty'):
            where &= counterparty == context['counterparty']
        if context.get('warehouse'):
            where &= shipment.warehouse == context['warehouse']
        if context.get('shipment_state'):
            where &= shipment.state == context['shipment_state']
        if context.get('lot_status'):
            where &= lot.lot_status == context['lot_status']

        return (
            lot
            .join(purchase_line, 'LEFT',
                condition=lot.line == purchase_line.id)
            .join(purchase, 'LEFT',
                condition=purchase_line.purchase == purchase.id)
            .join(sale_line, 'LEFT',
                condition=lot.sale_line == sale_line.id)
            .join(sale, 'LEFT',
                condition=sale_line.sale == sale.id)
            .join(shipment, 'LEFT',
                condition=lot.lot_shipment_in == shipment.id)
            .select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                lot.id.as_('id'),
                lot.id.as_('lot'),
                lot.lot_product.as_('product'),
                purchase.id.as_('purchase'),
                lot.line.as_('purchase_line'),
                sale.id.as_('sale'),
                lot.sale_line.as_('sale_line'),
                counterparty.as_('counterparty'),
                lot.lot_shipment_in.as_('shipment_in'),
                shipment.warehouse.as_('warehouse'),
                shipment.from_location.as_('from_location'),
                shipment.to_location.as_('to_location'),
                delivery_period.as_('delivery_period'),
                contract_from.as_('contract_from'),
                contract_to.as_('contract_to'),
                scheduled_date.as_('scheduled_date'),
                shipment.etad.as_('eta'),
                shipment.etd.as_('etd'),
                shipment.effective_date.as_('actual_date'),
                lot.lot_status.as_('lot_status'),
                shipment.state.as_('shipment_state'),
                Coalesce(lot.lot_qt, 0).as_('quantity'),
                lot.lot_unit_line.as_('unit'),
                where=where))


class CTRMFinanceDateContextMixin:
    from_date = fields.Date("From Date")
    to_date = fields.Date("To Date")
    party = fields.Many2One('party.party', "Party")
    currency = fields.Many2One('currency.currency', "Currency")

    @classmethod
    def default_to_date(cls):
        Date = Pool().get('ir.date')
        return Date.today()


class CTRMAccrualsContext(CTRMFinanceDateContextMixin, ModelView):
    "CTRM Accruals Context"
    __name__ = 'ctrm.reporting.finance.accruals.context'

    product = fields.Many2One('product.product', "Product")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    sale = fields.Many2One('sale.sale', "Sale")
    type = fields.Selection([
            (None, ''),
            ('physical', 'Physical'),
            ('fee', 'Fee'),
            ('derivative', 'Derivative'),
            ], "Accrual Type")


class CTRMAccruals(ModelSQL, ModelView):
    "CTRM Accruals"
    __name__ = 'ctrm.reporting.finance.accruals'

    valuation_date = fields.Date("Valuation Date")
    accrual_type = fields.Selection([
            ('physical', 'Physical'),
            ('fee', 'Fee'),
            ('derivative', 'Derivative'),
            ], "Accrual Type")
    lot = fields.Many2One('lot.lot', "Lot")
    product = fields.Many2One('product.product', "Product")
    party = fields.Many2One('party.party', "Party")
    purchase = fields.Many2One('purchase.purchase', "Purchase")
    purchase_line = fields.Many2One('purchase.line', "Purchase Line")
    sale = fields.Many2One('sale.sale', "Sale")
    sale_line = fields.Many2One('sale.line', "Sale Line")
    reference = fields.Char("Reference")
    quantity = fields.Numeric("Quantity", digits=(16, 5))
    unit = fields.Many2One('product.uom', "Unit")
    amount = fields.Numeric("Amount", digits=(16, 2))
    currency = fields.Many2One('currency.currency', "Currency")
    state = fields.Char("State")

    @classmethod
    def table_query(cls):
        pool = Pool()
        ValuationLine = pool.get('valuation.valuation.line')
        Lot = pool.get('lot.lot')
        val = ValuationLine.__table__()
        lot = Lot.__table__()

        accrual_type = Case(
            (val.type == 'derivative', 'derivative'),
            (val.type.in_(['line fee', 'pur. fee', 'sale fee',
                        'shipment fee']), 'fee'),
            else_='physical')
        context = Transaction().context
        where = (
            val.type.in_(
                PHYSICAL_VALUATION_TYPES + DERIVATIVE_VALUATION_TYPES)
            & ((val.lot == Null)
                | ((lot.invoice_line == Null)
                    & (lot.sale_invoice_line == Null))))
        if context.get('from_date'):
            where &= val.date >= context['from_date']
        if context.get('to_date'):
            where &= val.date <= context['to_date']
        if context.get('party'):
            where &= val.counterparty == context['party']
        if context.get('currency'):
            where &= val.currency == context['currency']
        if context.get('product'):
            where &= val.product == context['product']
        if context.get('purchase'):
            where &= val.purchase == context['purchase']
        if context.get('sale'):
            where &= val.sale == context['sale']
        if context.get('type'):
            where &= accrual_type == context['type']

        return (
            val
            .join(lot, 'LEFT', condition=val.lot == lot.id)
            .select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            val.id.as_('id'),
            val.date.as_('valuation_date'),
            accrual_type.as_('accrual_type'),
            val.lot.as_('lot'),
            val.product.as_('product'),
            val.counterparty.as_('party'),
            val.purchase.as_('purchase'),
            val.line.as_('purchase_line'),
            val.sale.as_('sale'),
            val.sale_line.as_('sale_line'),
            val.reference.as_('reference'),
            val.quantity.as_('quantity'),
            val.unit.as_('unit'),
            val.amount.as_('amount'),
            val.currency.as_('currency'),
            val.state.as_('state'),
            where=where))


class CTRMSettlementsContext(CTRMFinanceDateContextMixin, ModelView):
    "CTRM Settlements Context"
    __name__ = 'ctrm.reporting.finance.settlements.context'

    invoice_type = fields.Selection([
            (None, ''),
            ('in', 'Supplier'),
            ('out', 'Customer'),
            ], "Invoice Type")
    state = fields.Selection([
            (None, ''),
            ('draft', 'Draft'),
            ('validated', 'Validated'),
            ('posted', 'Posted'),
            ('paid', 'Paid'),
            ('cancelled', 'Cancelled'),
            ], "State")
    payment_status = fields.Selection([
            (None, ''),
            ('not', 'Not Paid'),
            ('partial', 'Partially Paid'),
            ('paid', 'Paid'),
            ], "Payment Status")


class CTRMSettlements(ModelSQL, ModelView):
    "CTRM Settlements and Invoicing"
    __name__ = 'ctrm.reporting.finance.settlements'

    invoice = fields.Many2One('account.invoice', "Invoice")
    invoice_type = fields.Selection([
            ('in', 'Supplier'),
            ('out', 'Customer'),
            ], "Invoice Type")
    number = fields.Char("Number")
    reference = fields.Char("Reference")
    invoice_date = fields.Date("Invoice Date")
    party = fields.Many2One('party.party', "Party")
    state = fields.Char("State")
    currency = fields.Many2One('currency.currency', "Currency")
    invoice_amount = fields.Numeric("Invoice Amount", digits=(16, 2))
    paid_amount = fields.Numeric("Paid Amount", digits=(16, 2))
    open_amount = fields.Numeric("Open Amount", digits=(16, 2))
    payment_status = fields.Selection([
            ('not', 'Not Paid'),
            ('partial', 'Partially Paid'),
            ('paid', 'Paid'),
            ], "Payment Status")
    move = fields.Many2One('account.move', "Move")
    reconciliation = fields.Integer("Reconciliation")

    @classmethod
    def table_query(cls):
        pool = Pool()
        Invoice = pool.get('account.invoice')
        InvoicePayment = pool.get('account.invoice-account.move.line')
        MoveLine = pool.get('account.move.line')

        invoice = Invoice.__table__()
        invoice_payment = InvoicePayment.__table__()
        line = MoveLine.__table__()

        amount = Coalesce(invoice.total_amount_cache, 0)
        paid = Coalesce(Sum(line.amount_second_currency), 0)
        open_amount = amount - paid
        payment_status = Case(
            (open_amount == 0, 'paid'),
            (paid != 0, 'partial'),
            else_='not')

        context = Transaction().context
        where = invoice.id != Null
        if context.get('from_date'):
            where &= invoice.invoice_date >= context['from_date']
        if context.get('to_date'):
            where &= invoice.invoice_date <= context['to_date']
        if context.get('party'):
            where &= invoice.party == context['party']
        if context.get('currency'):
            where &= invoice.currency == context['currency']
        if context.get('invoice_type'):
            where &= invoice.type == context['invoice_type']
        if context.get('state'):
            where &= invoice.state == context['state']

        grouped = (
            invoice
            .join(invoice_payment, 'LEFT',
                condition=invoice_payment.invoice == invoice.id)
            .join(line, 'LEFT',
                condition=line.id == invoice_payment.line)
            .select(
                invoice.id.as_('id'),
                invoice.id.as_('invoice'),
                invoice.type.as_('invoice_type'),
                invoice.number.as_('number'),
                invoice.reference.as_('reference'),
                invoice.invoice_date.as_('invoice_date'),
                invoice.party.as_('party'),
                invoice.state.as_('state'),
                invoice.currency.as_('currency'),
                amount.as_('invoice_amount'),
                paid.as_('paid_amount'),
                open_amount.as_('open_amount'),
                payment_status.as_('payment_status'),
                invoice.move.as_('move'),
                Max(line.reconciliation).as_('reconciliation'),
                where=where,
                group_by=[
                    invoice.id, invoice.type, invoice.number,
                    invoice.reference, invoice.invoice_date, invoice.party,
                    invoice.state, invoice.currency,
                    invoice.total_amount_cache, invoice.move,
                    ]))
        outer_where = grouped.id != Null
        if context.get('payment_status'):
            outer_where &= grouped.payment_status == context['payment_status']

        return grouped.select(
            Literal(0).as_('create_uid'),
            CurrentTimestamp().as_('create_date'),
            Literal(None).as_('write_uid'),
            Literal(None).as_('write_date'),
            grouped.id.as_('id'),
            grouped.invoice.as_('invoice'),
            grouped.invoice_type.as_('invoice_type'),
            grouped.number.as_('number'),
            grouped.reference.as_('reference'),
            grouped.invoice_date.as_('invoice_date'),
            grouped.party.as_('party'),
            grouped.state.as_('state'),
            grouped.currency.as_('currency'),
            grouped.invoice_amount.as_('invoice_amount'),
            grouped.paid_amount.as_('paid_amount'),
            grouped.open_amount.as_('open_amount'),
            grouped.payment_status.as_('payment_status'),
            grouped.move.as_('move'),
            grouped.reconciliation.as_('reconciliation'),
            where=outer_where)


class CTRMCashFlowContext(CTRMFinanceDateContextMixin, ModelView):
    "CTRM Cash Flow Context"
    __name__ = 'ctrm.reporting.finance.cashflow.context'

    overdue_only = fields.Boolean("Overdue Only")


class CTRMCashFlow(ModelSQL, ModelView):
    "CTRM Cash Flow Forecast"
    __name__ = 'ctrm.reporting.finance.cashflow'

    move_line = fields.Many2One('account.move.line', "Move Line")
    move = fields.Many2One('account.move', "Move")
    due_date = fields.Date("Due Date")
    posting_date = fields.Date("Posting Date")
    party = fields.Many2One('party.party', "Party")
    account = fields.Many2One('account.account', "Account")
    currency = fields.Many2One('currency.currency', "Currency")
    amount = fields.Numeric("Amount", digits=(16, 2))
    debit = fields.Numeric("Debit", digits=(16, 2))
    credit = fields.Numeric("Credit", digits=(16, 2))
    second_currency = fields.Many2One('currency.currency', "Second Currency")
    amount_second_currency = fields.Numeric(
        "Amount Second Currency", digits=(16, 2))
    description = fields.Char("Description")
    reference = fields.Char("Reference")
    journal = fields.Many2One('account.journal', "Journal")

    @classmethod
    def table_query(cls):
        pool = Pool()
        MoveLine = pool.get('account.move.line')
        Move = pool.get('account.move')
        Company = pool.get('company.company')

        line = MoveLine.__table__()
        move = Move.__table__()
        company = Company.__table__()
        currency = Coalesce(line.second_currency, company.currency)
        amount = Coalesce(line.debit, 0) - Coalesce(line.credit, 0)

        context = Transaction().context
        where = (
            (line.reconciliation == Null)
            & (line.maturity_date != Null))
        if context.get('from_date'):
            where &= line.maturity_date >= context['from_date']
        if context.get('to_date'):
            where &= line.maturity_date <= context['to_date']
        if context.get('party'):
            where &= line.party == context['party']
        if context.get('currency'):
            where &= currency == context['currency']
        if context.get('overdue_only'):
            Date = Pool().get('ir.date')
            where &= line.maturity_date < Date.today()

        return (
            line
            .join(move, condition=line.move == move.id)
            .join(company, condition=move.company == company.id)
            .select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                line.id.as_('id'),
                line.id.as_('move_line'),
                line.move.as_('move'),
                line.maturity_date.as_('due_date'),
                move.date.as_('posting_date'),
                line.party.as_('party'),
                line.account.as_('account'),
                currency.as_('currency'),
                amount.as_('amount'),
                line.debit.as_('debit'),
                line.credit.as_('credit'),
                line.second_currency.as_('second_currency'),
                line.amount_second_currency.as_('amount_second_currency'),
                line.description.as_('description'),
                Coalesce(line.ext_ref, move.ext_ref).as_('reference'),
                move.journal.as_('journal'),
                where=where))


class CTRMGLReconciliationContext(CTRMFinanceDateContextMixin, ModelView):
    "CTRM GL Reconciliation Context"
    __name__ = 'ctrm.reporting.finance.gl.context'

    account = fields.Many2One('account.account', "Account")
    lot = fields.Many2One('lot.lot', "Lot")
    unreconciled_only = fields.Boolean("Unreconciled Only")


class CTRMGLReconciliation(ModelSQL, ModelView):
    "CTRM GL Reconciliation"
    __name__ = 'ctrm.reporting.finance.gl'

    move_line = fields.Many2One('account.move.line', "Move Line")
    move = fields.Many2One('account.move', "Move")
    posting_date = fields.Date("Posting Date")
    journal = fields.Many2One('account.journal', "Journal")
    account = fields.Many2One('account.account', "Account")
    party = fields.Many2One('party.party', "Party")
    lot = fields.Many2One('lot.lot', "Lot")
    fee = fields.Many2One('fee.fee', "Fee")
    invoice = fields.Many2One('account.invoice', "Invoice")
    currency = fields.Many2One('currency.currency', "Currency")
    debit = fields.Numeric("Debit", digits=(16, 2))
    credit = fields.Numeric("Credit", digits=(16, 2))
    balance = fields.Numeric("Balance", digits=(16, 2))
    second_currency = fields.Many2One('currency.currency', "Second Currency")
    amount_second_currency = fields.Numeric(
        "Amount Second Currency", digits=(16, 2))
    reconciliation = fields.Integer("Reconciliation")
    move_state = fields.Char("Move State")
    description = fields.Char("Description")
    reference = fields.Char("Reference")

    @classmethod
    def table_query(cls):
        pool = Pool()
        MoveLine = pool.get('account.move.line')
        Move = pool.get('account.move')
        Invoice = pool.get('account.invoice')
        Company = pool.get('company.company')

        line = MoveLine.__table__()
        move = Move.__table__()
        invoice = Invoice.__table__()
        company = Company.__table__()
        currency = Coalesce(line.second_currency, company.currency)
        balance = Coalesce(line.debit, 0) - Coalesce(line.credit, 0)

        context = Transaction().context
        where = (line.lot != Null) | (line.fee != Null) | (invoice.id != Null)
        if context.get('from_date'):
            where &= move.date >= context['from_date']
        if context.get('to_date'):
            where &= move.date <= context['to_date']
        if context.get('party'):
            where &= line.party == context['party']
        if context.get('currency'):
            where &= currency == context['currency']
        if context.get('account'):
            where &= line.account == context['account']
        if context.get('lot'):
            where &= line.lot == context['lot']
        if context.get('unreconciled_only'):
            where &= line.reconciliation == Null

        return (
            line
            .join(move, condition=line.move == move.id)
            .join(company, condition=move.company == company.id)
            .join(invoice, 'LEFT', condition=invoice.move == move.id)
            .select(
                Literal(0).as_('create_uid'),
                CurrentTimestamp().as_('create_date'),
                Literal(None).as_('write_uid'),
                Literal(None).as_('write_date'),
                line.id.as_('id'),
                line.id.as_('move_line'),
                line.move.as_('move'),
                move.date.as_('posting_date'),
                move.journal.as_('journal'),
                line.account.as_('account'),
                line.party.as_('party'),
                line.lot.as_('lot'),
                line.fee.as_('fee'),
                invoice.id.as_('invoice'),
                currency.as_('currency'),
                line.debit.as_('debit'),
                line.credit.as_('credit'),
                balance.as_('balance'),
                line.second_currency.as_('second_currency'),
                line.amount_second_currency.as_('amount_second_currency'),
                line.reconciliation.as_('reconciliation'),
                move.state.as_('move_state'),
                line.description.as_('description'),
                Coalesce(line.ext_ref, move.ext_ref).as_('reference'),
                where=where))
