diff options
Diffstat (limited to 'src/FundLab.Api/Persistence.fs')
| -rw-r--r-- | src/FundLab.Api/Persistence.fs | 187 |
1 files changed, 186 insertions, 1 deletions
diff --git a/src/FundLab.Api/Persistence.fs b/src/FundLab.Api/Persistence.fs index e1cbb6d..7a7ab32 100644 --- a/src/FundLab.Api/Persistence.fs +++ b/src/FundLab.Api/Persistence.fs @@ -423,6 +423,28 @@ type RebalanceExecutionResult = Outcomes: RebalanceOrderOutcome list } +type RebalanceExecutionRecord = + { + PlanId: Guid + RunDate: DateOnly + InstrumentCode: string + Action: string + Amount: decimal + Status: string + OrderId: Guid option + PendingReason: string option + ExecutedAt: DateTimeOffset + } + +type RebalancePreview = + { + PlanId: Guid + RunDate: DateOnly + AvailableCash: decimal + Equity: decimal + Rows: RebalancePolicy.RebalanceWeightRow list + } + type DividendMode = DividendPolicy.DividendMode type DividendCommand = @@ -765,6 +787,19 @@ type FundRepository(connectionString: string) = PRIMARY KEY (plan_id, instrument_code) ); + CREATE TABLE IF NOT EXISTS rebalance_executions ( + plan_id uuid NOT NULL REFERENCES rebalance_plans(id), + run_date date NOT NULL, + instrument_code text NOT NULL, + action text NOT NULL, + amount numeric(20, 2) NOT NULL, + status text NOT NULL, + order_id uuid NULL, + pending_reason text NULL, + executed_at timestamptz NOT NULL DEFAULT now(), + PRIMARY KEY (plan_id, run_date, instrument_code) + ); + CREATE TABLE IF NOT EXISTS dividend_records ( id uuid PRIMARY KEY, fund_id uuid NOT NULL REFERENCES funds(id), @@ -2016,6 +2051,70 @@ type FundRepository(connectionString: string) = match RebalancePolicy.validateTargets command.Targets with | Error message -> Error message | Ok() -> Ok() + + let rebalanceExecutionRecordFromReader (reader: DbDataReader) : RebalanceExecutionRecord = + { + PlanId = reader.GetGuid(0) + RunDate = reader.GetFieldValue<DateOnly>(1) + InstrumentCode = reader.GetString(2) + Action = reader.GetString(3) + Amount = reader.GetDecimal(4) + Status = reader.GetString(5) + OrderId = if reader.IsDBNull(6) then None else Some(reader.GetGuid(6)) + PendingReason = readStringOption reader 7 + ExecutedAt = reader.GetFieldValue<DateTimeOffset>(8) + } + + let upsertRebalanceExecution + connection + transaction + (planId: Guid) + (runDate: DateOnly) + (outcome: RebalanceOrderOutcome) + = + let amount = + match Decimal.TryParse(outcome.Amount, NumberStyles.Float, CultureInfo.InvariantCulture) with + | true, value -> value + | false, _ -> 0m + + use command = + commandWithTransaction + connection + transaction + """ + INSERT INTO rebalance_executions (plan_id, run_date, instrument_code, action, amount, status, order_id, pending_reason) + VALUES (@plan_id, @run_date, @instrument_code, @action, @amount, @status, @order_id, @pending_reason) + ON CONFLICT (plan_id, run_date, instrument_code) DO UPDATE + SET action = EXCLUDED.action, + amount = EXCLUDED.amount, + status = EXCLUDED.status, + order_id = EXCLUDED.order_id, + pending_reason = EXCLUDED.pending_reason, + executed_at = now() + """ + + addParameter command "plan_id" NpgsqlDbType.Uuid (box planId) |> ignore + addParameter command "run_date" NpgsqlDbType.Date (box runDate) |> ignore + addParameter command "instrument_code" NpgsqlDbType.Text (box outcome.InstrumentCode) |> ignore + addParameter command "action" NpgsqlDbType.Text (box outcome.Action) |> ignore + addParameter command "amount" NpgsqlDbType.Numeric (box amount) |> ignore + addParameter command "status" NpgsqlDbType.Text (box outcome.Status) |> ignore + + let orderParameter = + match outcome.OrderId with + | Some value -> box value + | None -> box DBNull.Value + + addParameter command "order_id" NpgsqlDbType.Uuid orderParameter |> ignore + + let reasonParameter = + match outcome.PendingReason with + | Some value -> box value + | None -> box DBNull.Value + + addParameter command "pending_reason" NpgsqlDbType.Text reasonParameter |> ignore + command.ExecuteNonQuery() |> ignore + let dividendRecordFromReader (reader: DbDataReader) : DividendRecord = { Id = reader.GetGuid(0) @@ -3496,11 +3595,97 @@ type FundRepository(connectionString: string) = | other -> outcomes.Add({ InstrumentCode = diff.InstrumentCode; Action = "sell"; Amount = cashText diff.Amount; Status = "failed"; OrderId = None; PendingReason = Some (sprintf "%A" other) }) + let outcomeList = outcomes |> Seq.toList + + use persistTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted) + + try + for outcome in outcomeList do + upsertRebalanceExecution connection (Some persistTransaction) plan.Id runDate outcome + + persistTransaction.Commit() + with error -> + try + persistTransaction.Rollback() + with _ -> + () + + raise error + Ok { PlanId = plan.Id RunDate = runDate - Outcomes = outcomes |> Seq.toList + Outcomes = outcomeList + } + + member _.GetRebalanceExecutions(fundId: Guid) = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + use command = + commandWithTransaction + connection + None + """ + SELECT e.plan_id, e.run_date, e.instrument_code, e.action, e.amount, e.status, e.order_id, e.pending_reason, e.executed_at + FROM rebalance_executions e + JOIN rebalance_plans p ON p.id = e.plan_id + WHERE p.fund_id = @fund_id + ORDER BY e.executed_at DESC, e.run_date DESC, e.instrument_code + """ + + addParameter command "fund_id" NpgsqlDbType.Uuid (box fundId) |> ignore + + use reader = command.ExecuteReader() + let records = ResizeArray<RebalanceExecutionRecord>() + + while reader.Read() do + records.Add(rebalanceExecutionRecordFromReader reader) + + records |> Seq.toList + + member this.PreviewRebalancePlan(planId: Guid) : Result<RebalancePreview, string> = + use connection = new NpgsqlConnection(connectionString) + connection.Open() + + match findRebalancePlan connection None planId with + | None -> Error "rebalance plan was not found" + | Some plan -> + match this.GetFund plan.FundId with + | None -> Error "fund was not found" + | Some fund -> + let positions = this.GetFundPositions plan.FundId + + let buildSnapshot (position: FundPositionRecord) : RebalancePolicy.RebalancePositionSnapshot = + let marketValue = + match position.ValuationNav with + | Some nav -> Decimal.Round(position.Units * nav, 2) + | None -> 0m + + { + RebalancePolicy.RebalancePositionSnapshot.InstrumentCode = position.InstrumentCode + RebalancePolicy.RebalancePositionSnapshot.MarketValue = marketValue + RebalancePolicy.RebalancePositionSnapshot.Units = position.Units + RebalancePolicy.RebalancePositionSnapshot.AvailableUnits = position.Units - position.ReservedUnits + RebalancePolicy.RebalancePositionSnapshot.ValuationNav = position.ValuationNav + } + + let snapshots = positions |> List.map buildSnapshot + + match RebalancePolicy.weightRows plan.Targets snapshots fund.AvailableCash with + | Error message -> Error message + | Ok rows -> + let equity = + fund.AvailableCash + (snapshots |> List.sumBy (fun snapshot -> snapshot.MarketValue)) + + Ok + { + PlanId = plan.Id + RunDate = ConfirmationPolicy.tradeDateFor DateTimeOffset.UtcNow + AvailableCash = fund.AvailableCash + Equity = equity + Rows = rows } member this.RegisterDividend(idempotencyKey: string, fundId: Guid, command: DividendCommand) : DividendWriteResult = |