''
'' This code is part of Document Solutions for Word demos.
'' Copyright (c) MESCIUS inc. All rights reserved.
''
Imports System.Data
Imports System.Globalization
Imports System.IO
Imports GrapeCity.Documents.Word

'' This data template sample prints the closing disclosure page
'' of a loan application. It uses data from the sample database,
'' adding a few calculated values based on the sample data.
Public Class DataTplClosingDisclosure
    Function CreateDocx(Optional idx As Integer = 0) As GcWordDocument
        Using ds = New DataSet()
            '' Read the sample's data from DsWordTplDataSet.xml:
            ds.ReadXml(Path.Combine("Resources", "data", "DsWordTplDataSet.xml"))
            Dim dtClosing = ds.Tables("ClosingDisclosure")
            Dim row = dtClosing.Rows(0)

            '' Calculate monthly loan payments using a standard formula:
            Dim monthlyPay = CalcMonthlyPayment(Convert.ToDecimal(row("loanAmnt")), Convert.ToDecimal(row("loanInt")), Convert.ToInt32(row("loanTerm")))
            '' Other calculated data:
            Dim calc = New With {
                .totMon1 = monthlyPay + Convert.ToDecimal(row("insurance")) + Convert.ToDecimal(row("escrow")),
                .totMon8 = monthlyPay + Convert.ToDecimal(row("escrow")),
                .closingCosts = Convert.ToDecimal(row("loanCosts")) + Convert.ToDecimal(row("otherCosts")) - Convert.ToDecimal(row("lenderCredit"))
            }
            '' Combined data for the template:
            Dim data = New With {
                .issueDate = Convert.ToDateTime(row("issueDate")).ToString("s"),
                .closingDate = Convert.ToDateTime(row("closingDate")).ToString("s"),
                .disDate = Convert.ToDateTime(row("disDate")).ToString("s"),
                .agent = row("agent").ToString(),
                .fileNo = row("fileNo").ToString(),
                .propAddr = row("propAddr").ToString(),
                .salePrice = Convert.ToDecimal(row("salePrice")),
                .borrower = row("borrower").ToString(),
                .seller = row("seller").ToString(),
                .lender = row("lender").ToString(),
                .loanTerm = Convert.ToInt32(row("loanTerm")),
                .purpose = row("purpose").ToString(),
                .product = row("product").ToString(),
                .loanID = row("loanID").ToString(),
                .micNum = row("micNum").ToString(),
                .loanAmnt = Convert.ToDecimal(row("loanAmnt")),
                .loanInt = Convert.ToDecimal(row("loanInt")),
                .penalty = Convert.ToDecimal(row("penalty")),
                .insurance = Convert.ToDecimal(row("insurance")),
                .escrow = Convert.ToDecimal(row("escrow")),
                .taxes = Convert.ToDecimal(row("taxes")),
                .cash2close = Convert.ToDecimal(row("cash2close")),
                .loanCosts = Convert.ToDecimal(row("loanCosts")),
                .otherCosts = Convert.ToDecimal(row("otherCosts")),
                .lenderCredit = Convert.ToDecimal(row("lenderCredit")),
                monthlyPay,
                calc.totMon1,
                calc.totMon8,
                calc.closingCosts
            }

            '' Load the template DOCX, add the data source and process the template:
            Dim doc = New GcWordDocument()
            doc.Load(Path.Combine("Resources", "WordDocs", "Closing_Disclosure_Template.docx"))
            doc.DataTemplate.DataSources.Add("ds", data)
            doc.DataTemplate.Process(CultureInfo.GetCultureInfo("en-US"))

            '' Done:
            Return doc
        End Using
    End Function

    '' We use this formula to calculate loan payments for the sample:
    '' https://cdn.vertex42.com/ExcelArticles/Images/amortization-calculation-formula.png
    Private Shared Function CalcMonthlyPayment(principal As Decimal, yearlyInterest As Decimal, years As Integer) As Decimal
        Dim r = CDbl(yearlyInterest / 12)
        Dim n = 12 * years
        Dim q = Math.Pow(1 + r, n)
        Dim a = CDbl(principal) * ((r * q) / (q - 1))
        Return CDec(a)
    End Function

    Public Shared Function GetSampleParamsList() As List(Of String())
        Return New List(Of String())() From {
            New String() {"@data-templates/Closing Disclosure", "Generate the closing disclosure page of a loan application", Nothing},
            New String() {"@use-data-tpl/Closing Disclosure", "Generate the closing disclosure page of a loan application", Nothing}
        }
    End Function
End Class