testsCreateContract.ipynb

{
    "cells": [
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Load packages"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "#loading packages\n",
                "push!(LOAD_PATH, \"src\");\n",
                "import Base: @kwdef\n",
                "using Pkg\n",
                "Pkg.activate(\".\")\n",
                "using Test\n",
                "using LifeInsuranceDataModel\n",
                "using BitemporalPostgres\n",
                "using Logging\n",
                "using SearchLight\n",
                "using SearchLightPostgreSQL\n",
                "using TimeZones\n",
                "using ToStruct\n",
                "using JSON"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "setting your username and password - if not in on gitpod\n",
                "purging the data model entirely - empty the schema"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "if (haskey(ENV, \"GITPOD_REPO_ROOT\"))\n",
                "    run(```psql -f sqlsnippets/droptables.sql```)\n",
                "elseif (haskey(ENV, \"GENIE_ENV\") & (ENV[\"GENIE_ENV\"]==\"dev\"))r\n",
                "    run(```sudo -u postgres psql -f sqlsnippets/droptables.sql```)\n",
                "end\n"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Loading the data model- Create tables, constraints etc. and load Roles\n",
                "\n",
                "loading inverses of the role tables to provide role descriptions in object creation,  for instance like in: \"ref_role=cpRole[\"Policy Holder\"]"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "println(ENV[\"SEARCHLIGHT_USERNAME\"])\n",
                "println(ENV[\"SEARCHLIGHT_PASSWORD\"])\n",
                "\n",
                "LifeInsuranceDataModel.load_model()"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "cpRole = Dict{String,Int64}()\n",
                "map(find(LifeInsuranceDataModel.ContractPartnerRole)) do entry\n",
                "    cpRole[entry.value] = entry.id.value\n",
                "end\n",
                "tiprRole = Dict{String,Int64}()\n",
                "map(find(LifeInsuranceDataModel.TariffItemPartnerRole)) do entry\n",
                "    tiprRole[entry.value] = entry.id.value\n",
                "end\n",
                "titrRole = Dict{String,Int64}()\n",
                "map(find(LifeInsuranceDataModel.TariffItemRole)) do entry\n",
                "    titrRole[entry.value] = entry.id.value\n",
                "end\n",
                "\n",
                "ppRole = Dict{String,Int64}()\n",
                "map(find(LifeInsuranceDataModel.ProductPartRole)) do entry\n",
                "    ppRole[entry.value] = entry.id.value\n",
                "end\n"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Or just connect to an existing model"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "LifeInsuranceDataModel.connect()"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Create a Partner"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "p = LifeInsuranceDataModel.Partner()\n",
                "pr = LifeInsuranceDataModel.PartnerRevision(description=\"Partner 1\")\n",
                "w = Workflow(type_of_entity=\"Partner\",\n",
                "    tsw_validfrom=ZonedDateTime(2014, 5, 30, 21, 0, 1, 1, tz\"Africa/Porto-Novo\"),\n",
                ")\n",
                "create_entity!(w)\n",
                "create_component!(p, pr, w)\n",
                "commit_workflow!(w)\n",
                "\n",
                "Partner1 = p.id.value"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Create tariffs"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "# create Tariffs\n",
                "function create_tariff(dsc, mt)\n",
                "    t = LifeInsuranceDataModel.Tariff()\n",
                "    tr = LifeInsuranceDataModel.TariffRevision(description=dsc, mortality_table=mt)\n",
                "    w = Workflow(type_of_entity=\"Tariff\",\n",
                "        tsw_validfrom=ZonedDateTime(2014, 5, 30, 21, 0, 1, 1, tz\"Africa/Porto-Novo\"),\n",
                "    )\n",
                "    create_entity!(w)\n",
                "    create_component!(t, tr, w)\n",
                "    commit_workflow!(w)\n",
                "    t.id.value\n",
                "end\n",
                "\n",
                "LifeRiskTariff = create_tariff(\"Life Risk Insurance\", \"1980 CET - Male Nonsmoker, ANB\")\n",
                "TerminalIllnessTariff = create_tariff(\"Terminal Illness\", \"2001 VBT Residual Standard Select and Ultimate - Male Nonsmoker, ANB\")\n",
                "OccupationalDisabilityTariff = create_tariff(\"Occupational Disability\", \"2001 VBT Residual Standard Select and Ultimate - Male Nonsmoker, ANB\")\n",
                "ProfitParticipationTariff = create_tariff(\"Profit participation\", \"2001 VBT Residual Standard Select and Ultimate - Male Nonsmoker, ANB\")\n",
                "LifeRiskTariff2 = create_tariff(\"Life Risk Insurance\", \"2001 VBT Residual Standard Select and Ultimate - Male Nonsmoker, ANB\")\n",
                "\n",
                "find(TariffRevision)"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "find(Tariff, SQLWhereExpression(\"id=?\", ProfitParticipationTariff))\n",
                "find(Tariff, SQLWhereExpression(\"id=?\", TerminalIllnessTariff))"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Create Product"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "p = Product()\n",
                "pr = ProductRevision(description=\"Life Risk\")\n",
                "\n",
                "pp = ProductPart()\n",
                "ppr = ProductPartRevision(ref_tariff=LifeRiskTariff, ref_role=ppRole[\"Main Coverage - Life\"], description=\"Main Coverage - Life\")\n",
                "\n",
                "pp2 = ProductPart()\n",
                "ppr2 = ProductPartRevision(ref_tariff=ProfitParticipationTariff, ref_role=ppRole[\"Profit participation\"], description=\"Profit participation Lif Risk\")\n",
                "\n",
                "w0 = Workflow(type_of_entity=\"Product\",\n",
                "    tsw_validfrom=ZonedDateTime(2014, 5, 30, 21, 0, 1, 1, tz\"Africa/Porto-Novo\"),\n",
                ")\n",
                "create_entity!(w0)\n",
                "create_component!(p, pr, w0)\n",
                "create_subcomponent!(p, pp, ppr, w0)\n",
                "create_subcomponent!(p, pp2, ppr2, w0)\n",
                "commit_workflow!(w0)\n",
                "\n",
                "LifeRiskProduct = p.id.value\n",
                "println(LifeRiskProduct)\n",
                "\n",
                "p = Product()\n",
                "pr = ProductRevision(description=\"Life Risk - Terminal Illness\")\n",
                "\n",
                "pp = ProductPart()\n",
                "ppr = ProductPartRevision(ref_tariff=LifeRiskTariff, ref_role=ppRole[\"Main Coverage - Life\"], description=\"Main Coverage - Life\")\n",
                "\n",
                "pp2 = ProductPart()\n",
                "ppr2 = ProductPartRevision(ref_tariff=ProfitParticipationTariff, ref_role=ppRole[\"Profit participation\"], description=\"Profit participation Life Risk\")\n",
                "\n",
                "pp3 = ProductPart()\n",
                "ppr3 = ProductPartRevision(ref_tariff=TerminalIllnessTariff, ref_role=ppRole[\"Supplementary Coverage - Terminal Illness\"], description=\"additional cover Terminal Illness\")\n",
                "\n",
                "pp4 = ProductPart()\n",
                "ppr4 = ProductPartRevision(ref_tariff=ProfitParticipationTariff, ref_role=ppRole[\"Profit participation\"], description=\"Profit participation Terminal Illness\")\n",
                "\n",
                "pp5 = ProductPart()\n",
                "ppr5 = ProductPartRevision(ref_tariff=OccupationalDisabilityTariff, ref_role=ppRole[\"Supplementary Coverage - Occupational Disablity\"], description=\"additional cover Occupational Disablity\")\n",
                "\n",
                "pp6 = ProductPart()\n",
                "ppr6 = ProductPartRevision(ref_tariff=ProfitParticipationTariff, ref_role=ppRole[\"Profit participation\"], description=\"Profit participation Occ.Disablity\")\n",
                "\n",
                "\n",
                "\n",
                "w0 = Workflow(type_of_entity=\"Product\",\n",
                "    tsw_validfrom=ZonedDateTime(2014, 5, 30, 21, 0, 1, 1, tz\"Africa/Porto-Novo\"),\n",
                ")\n",
                "create_entity!(w0)\n",
                "create_component!(p, pr, w0)\n",
                "create_subcomponent!(p, pp, ppr, w0)\n",
                "create_subcomponent!(p, pp2, ppr2, w0)\n",
                "create_subcomponent!(p, pp3, ppr3, w0)\n",
                "create_subcomponent!(p, pp4, ppr4, w0)\n",
                "create_subcomponent!(p, pp5, ppr5, w0)\n",
                "create_subcomponent!(p, pp6, ppr6, w0)\n",
                "commit_workflow!(w0)\n",
                "\n",
                "LifeRiskTIODProduct = p.id.value\n",
                "println(LifeRiskTIODProduct)"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Testing"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "Create contract"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "w1 = Workflow(type_of_entity=\"Contract\",\n",
                "    tsw_validfrom=ZonedDateTime(2014, 5, 30, 21, 0, 1, 1, tz\"Africa/Porto-Novo\"),\n",
                ")\n",
                "\n",
                "create_entity!(w1)\n",
                "c = Contract()\n",
                "cr = ContractRevision(description=\"contract creation properties\")\n",
                "create_component!(c, cr, w1)\n",
                "\n",
                "cpr = ContractPartnerRef(ref_super=c.id)\n",
                "cprr = ContractPartnerRefRevision(ref_partner=Partner1, ref_role=cpRole[\"Policy Holder\"], description=\"policiyholder ref properties\")\n",
                "create_subcomponent!(c, cpr, cprr, w1)\n",
                "# pi 1\n",
                "LifeRiskTIODProduct = find(Product, SQLWhereExpression(\"id=?\", 2))[1].id.value\n",
                "PartnerRole = tiprRole[\"Insured Person\"]\n",
                "\n",
                "cpi = ProductItem(ref_super=c.id)\n",
                "cpir = ProductItemRevision(ref_product=LifeRiskTIODProduct, description=\"from contract creation\")\n",
                "create_subcomponent!(c, cpi, cpir, w1)\n",
                "\n",
                "LifeInsuranceDataModel.create_product_instance(w1, cpi, LifeRiskTIODProduct, Partner1, PartnerRole)\n",
                "\n",
                "commit_workflow!(w1)"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "update Contract yellow"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "cr1 = ContractRevision(ref_component=c.id, description=\"contract 1, 2nd mutation\")\n",
                "w2 = Workflow(type_of_entity=\"Contract\",\n",
                "    ref_history=w1.ref_history,\n",
                "    tsw_validfrom=ZonedDateTime(2016, 5, 30, 21, 0, 1, 1, tz\"Africa/Porto-Novo\"),\n",
                ")\n",
                "update_entity!(w2)\n",
                "update_component!(cr, cr1, w2)\n",
                "commit_workflow!(w2)"
            ]
        },
        {
            "cell_type": "markdown",
            "metadata": {},
            "source": [
                "   update Contract red"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "\n",
                "w3 = Workflow(type_of_entity=\"Contract\",\n",
                "    ref_history=w2.ref_history,\n",
                "    tsw_validfrom=ZonedDateTime(2015, 5, 30, 21, 0, 1, 1, tz\"Africa/Porto-Novo\"),\n",
                ")\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "update_entity!(w3)\n",
                "cr1 = findcomponentrevision(ContractRevision, c.id, w3.ref_version)[1]\n",
                "cr2 = ContractRevision(ref_component=c.id, description=\"contract 1, 3rd mutation retrospective\")\n",
                "update_component!(cr1, cr2, w3)\n",
                "commit_workflow!(w3)\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "w4 = Workflow(type_of_entity=\"Contract\",\n",
                "    ref_history=w2.ref_history,\n",
                "    tsw_validfrom=ZonedDateTime(2018, 5, 30, 21, 0, 1, 1, tz\"Africa/Porto-Novo\"),\n",
                ")\n",
                "update_entity!(w4)\n",
                "cr3 = ContractRevision(ref_component=c.id, description=\"contract 1, 4th mutation\")\n",
                "update_component!(cr2, cr3, w4)\n",
                "\n",
                "# pi 2\n",
                "LifeRiskTIODProduct = find(Product, SQLWhereExpression(\"id=?\", 2))[1].id.value\n",
                "Partner1\n",
                "PartnerRole = tiprRole[\"Insured Person\"]\n",
                "\n",
                "cpi = ProductItem(ref_super=c.id)\n",
                "cpir = ProductItemRevision(ref_product=LifeRiskTIODProduct, description=\"from contract 4th mutation\")\n",
                "create_subcomponent!(c, cpi, cpir, w4)\n",
                "\n",
                "LifeInsuranceDataModel.create_product_instance(w4, cpi, LifeRiskTIODProduct, Partner1, PartnerRole)\n",
                "\n",
                "commit_workflow!(w4)"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "using BitemporalPostgres, SearchLight\n",
                "history = 9\n",
                "txntime = MaxDate\n",
                "\n",
                "res = SearchLight.query(\"select s.tsdb_invalidfrom as sdbinv, m.tsdb_validfrom as mdbval, \n",
                "      m.tsworld_validfrom as mwval, s.tsworld_validfrom as swval,m.tsworld_invalidfrom as mwinv,s.tsworld_invalidfrom as swinv, \n",
                "      m.id as mid, s.id as sid , m.ref_history as mh , m.ref_version as mv , s.ref_version as sv\n",
                "      from validityintervals m join validityintervals s \n",
                "      on m.ref_history=s.ref_history\n",
                "      and m.ref_version != s.ref_version\n",
                "      and m.tsdb_validfrom = s.tsdb_invalidfrom\n",
                "      and m.tsworld_validfrom <= s.tsworld_validfrom \n",
                "      --and tstzrange(m.tsworld_validfrom, m.tsworld_invalidfrom) @> s.tsworld_validfrom -- tstzrange(s.tsworld_validfrom,s.tsworld_invalidfrom)\n",
                "      where m.ref_history=$history\n",
                "      and m.tsdb_invalidfrom = TIMESTAMPTZ '$txntime'\")\n",
                "\n",
                "println(res)\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": []
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": [
                "txns = SearchLight.query(\n",
                "    \"select tsdb_validfrom as vf from validityintervals union\n",
                "    select tsdb_invalidfrom as vf from validityintervals \n",
                "    group by vf order by vf\")\n",
                "\n",
                "refs = SearchLight.query(\n",
                "    \"select tsworld_validfrom as vf from validityintervals union\n",
                "    select tsworld_invalidfrom as vf from validityintervals \n",
                "    group by vf order by vf\")\n",
                "\n",
                "println(txns)\n",
                "\n",
                "println(refs)\n",
                "\n",
                "txnDict = Dict()\n",
                "for i = 1:first(size(txns))\n",
                "    txnDict[txns[i, 1]] = i\n",
                "end\n",
                "\n",
                "\n",
                "refDict = Dict()\n",
                "for i = 1:first(size(refs))\n",
                "    refDict[refs[i, 1]] = i\n",
                "end\n",
                "\n",
                "println(txnDict)\n",
                "\n",
                "println(refDict)\n",
                "\n",
                "using BitemporalPostgres\n",
                "valints = find(ValidityInterval)\n",
                "\n",
                "\n",
                "vi = valints[1]\n"
            ]
        },
        {
            "cell_type": "code",
            "execution_count": null,
            "metadata": {},
            "outputs": [],
            "source": []
        }
    ],
    "metadata": {
        "kernelspec": {
            "display_name": "Julia 1.8.2",
            "language": "julia",
            "name": "julia-1.8"
        },
        "language_info": {
            "file_extension": ".jl",
            "mimetype": "application/julia",
            "name": "julia",
            "version": "1.8.2"
        },
        "orig_nbformat": 4
    },
    "nbformat": 4,
    "nbformat_minor": 2
}