sheet-historical-data-conversion-sm/extractionCleansing/extractCleanData.go

package extractionCleansing

import (
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize/v2"
	"gullion/currency_convert_ssheets/models"
	utils "gullion/currency_convert_ssheets/utility"
	"strings"
	"time"
)


type FileFormatReader interface {
	ReadFromFile() [][]string
	GetFileMetaData() (string, string, string)
}

type FileXLXS struct {
	Path        string
	Sheet       string
	Measurement string
}

type FileJSON struct {
	Path string
}

//stub
func (fileJson *FileJSON) ReadFromFile() [][]string {

	var rawData [][]string
	return rawData
}

func (fileXLSX *FileXLXS) ReadFromFile() [][]string {

	//read from base xls file
	file, err := excelize.OpenFile(fileXLSX.Path)
	if err != nil {
		fmt.Println(err)
	}

	// Get all the rows in base file
	rows, err := file.GetRows(fileXLSX.Sheet)

	return rows
}

func (fileXLSX *FileXLXS) GetFileMetaData() (string, string, string) {

	return fileXLSX.Path, fileXLSX.Sheet, fileXLSX.Measurement
}

func (fileJson *FileJSON) GetFileMetaData() (string, string, string) {

	return fileJson.Path, "", ""
}

func ParseToExchangeRecordStruct(rawData [][]string, tag string) models.ExchangeRecords {
	ex := models.ExchangeRecords{}

	for rowIndex, row := range rawData {
		if rowIndex != 0 {

			dateValidator := &utils.XLXSDateValidator{Date: row[0]}
			fromValidator := &utils.CurrencyAmountValidator{Number: row[1]}
			toValidator := &utils.CurrencyAmountValidator{Number: row[2]}

			if utils.ValidateData(dateValidator) &&
				utils.ValidateData(fromValidator) && utils.ValidateData(toValidator) {
				date, _ := excelize.ExcelDateToTime(utils.StringToFloat(row[0]), false)
				fromCurrency := utils.StringToFloat(row[1])
				toCurrency := utils.StringToFloat(row[2])

				ex.ExchangeRecords = append(ex.ExchangeRecords,
					models.ExchangeRecord{Date: date, Tag: tag, FromCurrency: fromCurrency, ToCurrency: toCurrency})
			}
		}
	}

	ex.ExchangeRecords = fillInMissingDates(ex.ExchangeRecords)
	
	return ex
}

func fillInMissingDates(exchangeRecords []models.ExchangeRecord) []models.ExchangeRecord {
	xlsxFile := &FileXLXS{
		Path:  "assets/historicaldata/2010-to-2020-dates.xlsx",
		Sheet: "Sheet1",
	}
	rawData := xlsxFile.ReadFromFile()

	var allDate []time.Time

	for rowIndex, row := range rawData {// get All dates of years
		if rowIndex != 0 {

			dateValidator := &utils.XLXSDateValidator{Date: row[0]}

			if utils.ValidateData(dateValidator)  {
				date, _ := excelize.ExcelDateToTime(utils.StringToFloat(row[0]), false)

				allDate = append(allDate, date)
			}
		}
	}


	hashMapSourceBKMRecords := make(map[time.Time]models.ExchangeRecord)
	var newBkmRecordList []models.ExchangeRecord
	minDateTo := exchangeRecords[len(exchangeRecords)-1].Date

	for _, bkmRecord := range exchangeRecords {
		hashMapSourceBKMRecords[bkmRecord.Date] = bkmRecord
	}

	for _, toDate := range allDate {
		if minDateTo == toDate {
			break
		}
		day := toDate
		for {

			if _, ok := hashMapSourceBKMRecords[day]; ok {
				break
			}
			day = day.AddDate(0, 0, -1)
		}

		newRecord := hashMapSourceBKMRecords[day]
		newRecord.Date = toDate

		newBkmRecordList = append(newBkmRecordList, newRecord)
	}
	return newBkmRecordList
}

func ParseToBkmRecordStruct(rawData [][]string) models.BKMRecords {
	bkmRecords := models.BKMRecords{}
	var currentYearForExchangeConversion string

	for _, row := range rawData {

		//ateValidator := &utils.XLXSDateValidator{Date: row[0]}
		//
		//if utils.ValidateData(dateValidator) {
		bkmRecord, isRecord := transformBkmCellData(row, &currentYearForExchangeConversion)
		if isRecord {
			bkmRecords.BKMRecords = append(bkmRecords.BKMRecords,
				bkmRecord)
		}
		//}
	}
	return bkmRecords
}

func transformBkmCellData(row []string, currentYearForExchangeConversion *string) (models.BKMRecord, bool) {
	bkmRecord := models.BKMRecord{}
	numberOfTransactions := models.Transaction{}
	volumeOfTransactions := models.Transaction{}
	isRecord := false
	for i, _ := range row {
		row[i] = utils.ConvertDecimalCommaToDecimalPoint(row[i])

		if strings.Contains(row[0], "year") {
			*currentYearForExchangeConversion = strings.ReplaceAll(row[0], " year", "")
		}

		// extract transactions data
		row[i] = utils.FilterNumberString(row[i])
		if i >= 1 && i <= 6 {
			if !utils.IsNumeric(row[i]) {
				isRecord = false
				break
			}
		}

		if (i >= 1 && i <= 6) && utils.IsNumeric(row[i]) {

			layoutUS := "January 2, 2006"
			date := row[0] + " 01, " + *currentYearForExchangeConversion
			t, err := time.Parse(layoutUS, date)
			if err != nil {
				isRecord = false
				break
			}
			isRecord = true
			bkmRecord.Date = t

			switch i {
			case 4:
				volumeOfTransactions.Purchase = utils.StringToFloat(row[i])
			case 5:
				volumeOfTransactions.Cash = utils.StringToFloat(row[i])
			case 6:
				volumeOfTransactions.Total = utils.StringToFloat(row[i])
			}

			switch i {
			case 1:
				numberOfTransactions.Purchase = utils.StringToFloat(row[i])
			case 2:
				numberOfTransactions.Cash = utils.StringToFloat(row[i])
			case 3:
				numberOfTransactions.Total = utils.StringToFloat(row[i])
			}

		}
	}
	bkmRecord.NumberOfTransactions = numberOfTransactions
	bkmRecord.VolumeOfTransactions = volumeOfTransactions

	return bkmRecord, isRecord
}