249 lines
13 KiB
Go
249 lines
13 KiB
Go
|
package reportgen
|
||
|
|
|
||
|
|
import (
|
||
|
|
"fmt"
|
||
|
|
"gullion/currency_convert_ssheets/configs"
|
||
|
|
"gullion/currency_convert_ssheets/models"
|
||
|
|
"gullion/currency_convert_ssheets/timeSeriesDB"
|
||
|
|
"gullion/currency_convert_ssheets/utility"
|
||
|
|
"strconv"
|
||
|
|
"unicode/utf8"
|
||
|
|
|
||
|
|
"github.com/360EntSecGroup-Skylar/excelize/v2"
|
||
|
|
"golang.org/x/text/language"
|
||
|
|
)
|
||
|
|
|
||
|
|
const mergedCellStyle = `{"font":{"bold":true},"alignment":{"horizontal":"center","wrap_text":true,"ident":1,"justify_last_line":true,"reading_order":0,"relative_indent":1}}`
|
||
|
|
const normalCellStyle = `{"alignment":{"horizontal":"center","ident":1,"justify_last_line":true,"reading_order":0,"relative_indent":1}}`
|
||
|
|
const unitXAU = 1000
|
||
|
|
const precisionCurrencyOutput = 2
|
||
|
|
|
||
|
|
func GenerateReport(inputFile configs.InputFile, config configs.Configurations) {
|
||
|
|
isRawReport := false
|
||
|
|
if !config.YearlySummary && !config.QuarterlySummary {
|
||
|
|
isRawReport = true
|
||
|
|
}
|
||
|
|
|
||
|
|
outPutCurrencyTag := language.English
|
||
|
|
|
||
|
|
if config.OutputCurrencyFormat == "TRY" {
|
||
|
|
outPutCurrencyTag = language.Turkish
|
||
|
|
}
|
||
|
|
|
||
|
|
//create new file for label conversion
|
||
|
|
fileLabelConversion := excelize.NewFile()
|
||
|
|
index := fileLabelConversion.NewSheet(inputFile.SheetName)
|
||
|
|
|
||
|
|
haspMap := timeSeriesDB.ReadBKMRecords(inputFile.Measurement)
|
||
|
|
|
||
|
|
keys := utility.GetSortedKeysListBKMRecords(haspMap)
|
||
|
|
|
||
|
|
haspMapUsdTl := timeSeriesDB.ReadExchangeRecordsMonthly("USDTL", config.DailyToMonthlyAggregationMethod)
|
||
|
|
haspMapXAUTL := timeSeriesDB.ReadExchangeRecordsMonthly("XAUTL", config.DailyToMonthlyAggregationMethod)
|
||
|
|
|
||
|
|
rowIndex := 1
|
||
|
|
for i, key := range keys {
|
||
|
|
if !isRawReport {
|
||
|
|
addYearRow(key, &rowIndex, fileLabelConversion, inputFile.SheetName)
|
||
|
|
}
|
||
|
|
|
||
|
|
if i == 0 || !isRawReport {
|
||
|
|
addInfoRow(&rowIndex, fileLabelConversion, inputFile.SheetName)
|
||
|
|
}
|
||
|
|
|
||
|
|
quarterNumberTransactionTL := models.Transaction{}
|
||
|
|
quarterVolumeTransactionTL := models.Transaction{}
|
||
|
|
yearTotalNumberTransactionTL := models.Transaction{}
|
||
|
|
yearTotalVolumeTransactionTL := models.Transaction{}
|
||
|
|
|
||
|
|
quarterVolumeTransactionUSD := models.Transaction{}
|
||
|
|
yearTotalVolumeTransactionUSD := models.Transaction{}
|
||
|
|
|
||
|
|
quarterVolumeTransactionXAU := models.Transaction{}
|
||
|
|
yearTotalVolumeTransactionXAU := models.Transaction{}
|
||
|
|
|
||
|
|
fileLabelConversion.SetColWidth(inputFile.SheetName, "A", "M", 12)
|
||
|
|
|
||
|
|
monthlyRecordsBKM := haspMap[key]
|
||
|
|
var reversedMonthlyRecordBKM []models.BKMRecord
|
||
|
|
|
||
|
|
for i := range monthlyRecordsBKM {
|
||
|
|
n := monthlyRecordsBKM[len(monthlyRecordsBKM)-1-i]
|
||
|
|
//fmt.Println(n) -- sanity check
|
||
|
|
reversedMonthlyRecordBKM = append(reversedMonthlyRecordBKM, n)
|
||
|
|
}
|
||
|
|
|
||
|
|
for i, record := range reversedMonthlyRecordBKM {
|
||
|
|
cellName := "A" + strconv.Itoa(rowIndex)
|
||
|
|
|
||
|
|
var month string
|
||
|
|
if isRawReport {
|
||
|
|
month = fmt.Sprintf("%v", utility.DateToMonth(record.Date))[:3] + " " + strconv.Itoa(utility.DateToYear(record.Date))
|
||
|
|
} else {
|
||
|
|
month = fmt.Sprintf("%v", utility.DateToMonth(record.Date))
|
||
|
|
}
|
||
|
|
|
||
|
|
keyYearMonth := strconv.Itoa(utility.DateToYear(record.Date)) + " " + strconv.Itoa(int(utility.DateToMonth(record.Date)))
|
||
|
|
|
||
|
|
rateUSDTL := haspMapUsdTl[keyYearMonth].ToCurrency
|
||
|
|
rateXAUTL := haspMapXAUTL[keyYearMonth].ToCurrency
|
||
|
|
|
||
|
|
row := []string{month,
|
||
|
|
utility.FloatToFormattedString(record.NumberOfTransactions.Purchase, 0, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(record.NumberOfTransactions.Cash, 0, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(record.NumberOfTransactions.Total, 0, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(record.VolumeOfTransactions.Purchase, precisionCurrencyOutput, outPutCurrencyTag), // transactions
|
||
|
|
utility.FloatToFormattedString(record.VolumeOfTransactions.Cash, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(record.VolumeOfTransactions.Total, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(record.VolumeOfTransactions.Purchase/rateUSDTL, precisionCurrencyOutput, outPutCurrencyTag), //usd
|
||
|
|
utility.FloatToFormattedString(record.VolumeOfTransactions.Cash/rateUSDTL, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(record.VolumeOfTransactions.Total/rateUSDTL, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(record.VolumeOfTransactions.Purchase*unitXAU/rateXAUTL, precisionCurrencyOutput, outPutCurrencyTag), //XAU
|
||
|
|
utility.FloatToFormattedString(record.VolumeOfTransactions.Cash*unitXAU/rateXAUTL, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(record.VolumeOfTransactions.Total*unitXAU/rateXAUTL, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
}
|
||
|
|
fileLabelConversion.SetSheetRow(inputFile.SheetName, cellName, &row)
|
||
|
|
style, _ := fileLabelConversion.NewStyle(normalCellStyle)
|
||
|
|
fileLabelConversion.SetCellStyle(inputFile.SheetName, cellName, "M"+trimFirstRune(cellName), style)
|
||
|
|
rowIndex++
|
||
|
|
|
||
|
|
quarterNumberTransactionTL = aggregateTransactions(&quarterNumberTransactionTL, record.NumberOfTransactions, 1)
|
||
|
|
quarterVolumeTransactionTL = aggregateTransactions(&quarterVolumeTransactionTL, record.VolumeOfTransactions, 1)
|
||
|
|
yearTotalNumberTransactionTL = aggregateTransactions(&yearTotalNumberTransactionTL, record.NumberOfTransactions, 1)
|
||
|
|
yearTotalVolumeTransactionTL = aggregateTransactions(&yearTotalVolumeTransactionTL, record.VolumeOfTransactions, 1)
|
||
|
|
|
||
|
|
quarterVolumeTransactionUSD = aggregateTransactions(&quarterVolumeTransactionUSD, record.VolumeOfTransactions, rateUSDTL)
|
||
|
|
yearTotalVolumeTransactionUSD = aggregateTransactions(&yearTotalVolumeTransactionUSD, record.VolumeOfTransactions, rateUSDTL)
|
||
|
|
|
||
|
|
quarterVolumeTransactionXAU = aggregateTransactions(&quarterVolumeTransactionXAU, record.VolumeOfTransactions, rateXAUTL)
|
||
|
|
yearTotalVolumeTransactionXAU = aggregateTransactions(&yearTotalVolumeTransactionXAU, record.VolumeOfTransactions, rateXAUTL)
|
||
|
|
|
||
|
|
if (i+1)%3 == 0 && config.QuarterlySummary { //Quarter
|
||
|
|
addQuarterTotal(quarterNumberTransactionTL, quarterVolumeTransactionTL,
|
||
|
|
quarterVolumeTransactionUSD, quarterVolumeTransactionXAU, &rowIndex, fileLabelConversion,
|
||
|
|
inputFile.SheetName, (i+1)/3, outPutCurrencyTag)
|
||
|
|
quarterNumberTransactionTL = models.Transaction{}
|
||
|
|
quarterVolumeTransactionTL = models.Transaction{}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
//year
|
||
|
|
if config.YearlySummary {
|
||
|
|
addYearTotal(yearTotalNumberTransactionTL, yearTotalVolumeTransactionTL,
|
||
|
|
yearTotalVolumeTransactionUSD, yearTotalVolumeTransactionXAU,
|
||
|
|
&rowIndex, fileLabelConversion, inputFile.SheetName, key, outPutCurrencyTag)
|
||
|
|
yearTotalNumberTransactionTL = models.Transaction{}
|
||
|
|
yearTotalVolumeTransactionTL = models.Transaction{}
|
||
|
|
}
|
||
|
|
if !isRawReport {
|
||
|
|
addEmptyRow(&rowIndex, fileLabelConversion, inputFile.SheetName)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// Set active sheet of the workbook.
|
||
|
|
fileLabelConversion.SetActiveSheet(index)
|
||
|
|
// Save xlsx file by the given path.
|
||
|
|
if err := fileLabelConversion.SaveAs("reports-output/" + inputFile.FileName + ".xlsx"); err != nil {
|
||
|
|
fmt.Println(err)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
func aggregateTransactions(quarterTransactions *models.Transaction, transaction models.Transaction, exchangeRate float64) models.Transaction {
|
||
|
|
quarterTransactions.Total += transaction.Total / exchangeRate
|
||
|
|
quarterTransactions.Cash += transaction.Cash / exchangeRate
|
||
|
|
quarterTransactions.Purchase += transaction.Purchase / exchangeRate
|
||
|
|
|
||
|
|
return *quarterTransactions
|
||
|
|
}
|
||
|
|
|
||
|
|
func addQuarterTotal(quarterNumberTransactionTL models.Transaction, quarterVolumeTransactionTL models.Transaction,
|
||
|
|
quarterVolumeTransactionUSD models.Transaction, quarterVolumeTransactionXAU models.Transaction, rowIndex *int,
|
||
|
|
fileLabelConversion *excelize.File, sheetName string, quarter int, outPutCurrencyTag language.Tag) {
|
||
|
|
cellName := "A" + strconv.Itoa(*rowIndex)
|
||
|
|
|
||
|
|
row := []string{"Quarter " + strconv.Itoa(quarter),
|
||
|
|
utility.FloatToFormattedString(quarterNumberTransactionTL.Purchase, 0, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(quarterNumberTransactionTL.Cash, 0, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(quarterNumberTransactionTL.Total, 0, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(quarterVolumeTransactionTL.Purchase, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(quarterVolumeTransactionTL.Cash, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(quarterVolumeTransactionTL.Total, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(quarterVolumeTransactionUSD.Purchase, precisionCurrencyOutput, outPutCurrencyTag), //usd
|
||
|
|
utility.FloatToFormattedString(quarterVolumeTransactionUSD.Cash, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(quarterVolumeTransactionUSD.Total, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(quarterVolumeTransactionXAU.Purchase*unitXAU, precisionCurrencyOutput, outPutCurrencyTag), //XAU
|
||
|
|
utility.FloatToFormattedString(quarterVolumeTransactionXAU.Cash*unitXAU, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(quarterVolumeTransactionXAU.Total*unitXAU, precisionCurrencyOutput, outPutCurrencyTag)}
|
||
|
|
|
||
|
|
fileLabelConversion.SetSheetRow(sheetName, cellName, &row)
|
||
|
|
style, _ := fileLabelConversion.NewStyle(mergedCellStyle)
|
||
|
|
fileLabelConversion.SetCellStyle(sheetName, cellName, "M"+trimFirstRune(cellName), style)
|
||
|
|
*rowIndex++
|
||
|
|
}
|
||
|
|
|
||
|
|
func trimFirstRune(s string) string {
|
||
|
|
_, i := utf8.DecodeRuneInString(s)
|
||
|
|
return s[i:]
|
||
|
|
}
|
||
|
|
|
||
|
|
func addYearTotal(yearTotalNumberTransaction models.Transaction, yearTotalVolumeTransaction models.Transaction,
|
||
|
|
yearTotalVolumeTransactionUSD models.Transaction, yearTotalVolumeTransactionXAU models.Transaction,
|
||
|
|
rowIndex *int, fileLabelConversion *excelize.File, sheetName string, year int, outPutCurrencyTag language.Tag) {
|
||
|
|
cellName := "A" + strconv.Itoa(*rowIndex)
|
||
|
|
|
||
|
|
row := []string{strconv.Itoa(year) + " Year",
|
||
|
|
utility.FloatToFormattedString(yearTotalNumberTransaction.Purchase, 0, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(yearTotalNumberTransaction.Cash, 0, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(yearTotalNumberTransaction.Total, 0, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(yearTotalVolumeTransaction.Purchase, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(yearTotalVolumeTransaction.Cash, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(yearTotalVolumeTransaction.Total, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(yearTotalVolumeTransactionUSD.Purchase, precisionCurrencyOutput, outPutCurrencyTag), //usd
|
||
|
|
utility.FloatToFormattedString(yearTotalVolumeTransactionUSD.Cash, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(yearTotalVolumeTransactionUSD.Total, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(yearTotalVolumeTransactionXAU.Purchase*unitXAU, precisionCurrencyOutput, outPutCurrencyTag), //XAU
|
||
|
|
utility.FloatToFormattedString(yearTotalVolumeTransactionXAU.Cash*unitXAU, precisionCurrencyOutput, outPutCurrencyTag),
|
||
|
|
utility.FloatToFormattedString(yearTotalVolumeTransactionXAU.Total*unitXAU, precisionCurrencyOutput, outPutCurrencyTag)}
|
||
|
|
fileLabelConversion.SetSheetRow(sheetName, cellName, &row)
|
||
|
|
style, _ := fileLabelConversion.NewStyle(mergedCellStyle)
|
||
|
|
fileLabelConversion.SetCellStyle(sheetName, cellName, "M"+trimFirstRune(cellName), style)
|
||
|
|
*rowIndex++
|
||
|
|
}
|
||
|
|
|
||
|
|
func addYearRow(key int, rowIndex *int, fileLabelConversion *excelize.File, sheetName string) {
|
||
|
|
cellName := "A" + strconv.Itoa(*rowIndex)
|
||
|
|
row := []string{strconv.Itoa(key) + " Year"}
|
||
|
|
fileLabelConversion.SetSheetRow(sheetName, cellName, &row)
|
||
|
|
style, _ := fileLabelConversion.NewStyle(mergedCellStyle)
|
||
|
|
fileLabelConversion.SetCellStyle(sheetName, cellName, "M"+trimFirstRune(cellName), style)
|
||
|
|
fileLabelConversion.MergeCell(sheetName, cellName, "M"+trimFirstRune(cellName))
|
||
|
|
*rowIndex++
|
||
|
|
}
|
||
|
|
|
||
|
|
func addEmptyRow(rowIndex *int, fileLabelConversion *excelize.File, sheetName string) {
|
||
|
|
cellName := "A" + strconv.Itoa(*rowIndex)
|
||
|
|
row := []string{" "}
|
||
|
|
fileLabelConversion.SetSheetRow(sheetName, cellName, &row)
|
||
|
|
*rowIndex++
|
||
|
|
}
|
||
|
|
|
||
|
|
func addInfoRow(rowIndex *int, fileLabelConversion *excelize.File, sheetName string) {
|
||
|
|
cellName := "A" + strconv.Itoa(*rowIndex)
|
||
|
|
row := []string{"Period", "Number of transaction over period", " ", " ", "Volume of transactions (Million TL)",
|
||
|
|
" ", " ", "Volume of transactions (Million USD)", " ", " ", "Volume of transactions (Thousand XAU)"}
|
||
|
|
fileLabelConversion.SetSheetRow(sheetName, cellName, &row)
|
||
|
|
style, _ := fileLabelConversion.NewStyle(mergedCellStyle)
|
||
|
|
fileLabelConversion.SetCellStyle(sheetName, cellName, "M"+trimFirstRune(cellName), style)
|
||
|
|
fileLabelConversion.MergeCell(sheetName, "B"+trimFirstRune(cellName), "D"+trimFirstRune(cellName))
|
||
|
|
fileLabelConversion.MergeCell(sheetName, "E"+trimFirstRune(cellName), "G"+trimFirstRune(cellName))
|
||
|
|
fileLabelConversion.MergeCell(sheetName, "H"+trimFirstRune(cellName), "J"+trimFirstRune(cellName))
|
||
|
|
fileLabelConversion.MergeCell(sheetName, "K"+trimFirstRune(cellName), "M"+trimFirstRune(cellName))
|
||
|
|
*rowIndex++
|
||
|
|
|
||
|
|
cellName = "A" + strconv.Itoa(*rowIndex)
|
||
|
|
row = []string{"", "Purchase", "Cash", "Total", "Purchase", "Cash", "Total",
|
||
|
|
"Purchase", "Cash", "Total", "Purchase", "Cash", "Total"}
|
||
|
|
fileLabelConversion.SetSheetRow(sheetName, cellName, &row)
|
||
|
|
fileLabelConversion.SetCellStyle(sheetName, cellName, "M"+trimFirstRune(cellName), style)
|
||
|
|
*rowIndex++
|
||
|
|
}
|