func metricsList(v map[uint32]float32) (metrics []metric) {
	dividaBruta := v[p.DividaCirc] + v[p.DividaNCirc]
	caixa := v[p.Caixa] + v[p.AplicFinanceiras]
	dividaLiquida := dividaBruta - caixa
	EBITDA := v[p.EBIT] - v[p.Deprec]
	proventos := v[p.Dividendos] + v[p.JurosCapProp]
	var roe float32
	if v[p.LucLiq] > 0 && v[p.EquityAvg] > 0 {
		roe = zeroIfNeg(safeDiv(v[p.LucLiq], v[p.EquityAvg]))
	}
	var cg float32 = v[p.AtivoCirc] - v[p.PassivoCirc]
	var st float32 = v[p.Caixa] + v[p.AplicFinanceiras] - (v[p.DividaCirc] + v[p.DividendosJCP] + v[p.DividendosMin])
	var ncg float32 = cg - st
	var lpa float32 = safeDiv(v[p.LucLiq]*v[p.Escala], v[p.Shares])
	return []metric{
		{"Patrimônio Líquido", v[p.Equity], NUMBER, grpAccts},
		{"", 0, EMPTY, grpAccts},
		{"Receita Líquida", v[p.Vendas], NUMBER, grpAccts},
		{"EBITDA", EBITDA, NUMBER, grpAccts},
		{"EBIT", v[p.EBIT], NUMBER, grpAccts},
		{"Resultado Financeiro", v[p.ResulFinanc], NUMBER, grpAccts},
		{"Operações Descontinuadas", v[p.ResulOpDescont], NUMBER, grpAccts},
		{"Lucro Líquido", v[p.LucLiq], NUMBER, grpAccts},
		{"", 0, EMPTY, grpAccts},
		{"LPA", lpa, INDEX, grpAccts},
		{"VPA", safeDiv(v[p.Equity]*v[p.Escala], v[p.Shares]), INDEX, grpAccts},
		{"P/L", safeDiv(v[p.Quote], lpa), INDEX, grpAccts},
		{"Cotação", v[p.Quote], INDEX, grpAccts},
		{"", 0, EMPTY, grpAccts},
		{"Marg. EBITDA", zeroIfNeg(safeDiv(EBITDA, v[p.Vendas])), PERCENT, grpAccts},
		{"Marg. EBIT", zeroIfNeg(safeDiv(v[p.EBIT], v[p.Vendas])), PERCENT, grpAccts},
		{"Marg. Líq.", zeroIfNeg(safeDiv(v[p.LucLiq], v[p.Vendas])), PERCENT, grpAccts},
		{"ROE", roe, PERCENT, grpAccts},
		{"", 0, EMPTY, grpAccts},
		{"Caixa", caixa, NUMBER, grpAccts},
		{"Dívida Bruta", dividaBruta, NUMBER, grpAccts},
		{"Dívida Líq.", dividaLiquida, NUMBER, grpAccts},
		{"Dív. Bru./PL", zeroIfNeg(safeDiv(dividaBruta, v[p.Equity])), PERCENT, grpAccts},
		{"Dív.Líq./EBITDA", zeroIfNeg(safeDiv(dividaLiquida, EBITDA)), INDEX, grpAccts},
		{"", 0, EMPTY, grpAccts},
		{"FCO", v[p.FCO], NUMBER, grpAccts},
		{"FCI", v[p.FCI], NUMBER, grpAccts},
		{"FCF", v[p.FCF], NUMBER, grpAccts},
		{"FCT", v[p.FCO] + v[p.FCI] + v[p.FCF], NUMBER, grpAccts},
		{"FCL (FCO+FCI)", v[p.FCO] + v[p.FCI], NUMBER, grpAccts},
		{"", 0, EMPTY, grpAccts},
		{"Proventos", proventos, NUMBER, grpAccts},
		{"Payout", zeroIfNeg(safeDiv(proventos, v[p.LucLiq])), PERCENT, grpAccts},
		{"", 0, EMPTY, grpAccts},
		{"Total de Ações", v[p.Shares], GENERAL, grpShares},
		{"Free Float", v[p.FreeFloat], PERCENT, grpShares},
		{"", 0, EMPTY, grpShares},
		{"Liquidez Corrente (Ativo Circ./Passivo Circ.)", safeDiv(v[p.AtivoCirc], v[p.PassivoCirc]), INDEX, grpExtra},
		{"Liquidez Seco [(Ativo Circ.-Estoque)/Passivo Circ.]", safeDiv(v[p.AtivoCirc]-v[p.Estoque], v[p.PassivoCirc]), INDEX, grpExtra},
		{"Giro dos Ativos (Vendas/Ativo)", safeDiv(v[p.Vendas], v[p.AtivoTotal]), INDEX, grpExtra},
		{"", 0, EMPTY, grpExtra},
		{"Giro de Estoque (dias)", safeDiv(v[p.EstoqueMedio], -v[p.CustoVendas]/360), INDEX, grpExtra},
		{"Prazo Médio de Recebimento (dias)", safeDiv(v[p.ContasARecebCirc]+v[p.ContasARecebNCirc], v[p.Vendas]/360), INDEX, grpExtra},
		{"", 0, EMPTY, grpExtra},
		{"Poder de Ganho Básico (EBITDA/Ativo)", safeDiv(EBITDA, v[p.AtivoTotal]), PERCENT, grpExtra},
		{"ROA", safeDiv(v[p.LucLiq], v[p.AtivoTotal]), PERCENT, grpExtra},
		{"ROE", roe, PERCENT, grpExtra},
		{"", 0, EMPTY, grpExtra},
		{"Capital de Giro (CG)", cg, NUMBER, grpFleuriet},
		{"Saldo de Tesouraria (ST)", st, NUMBER, grpFleuriet},
		{"Necessidade de Capital de Giro (NCG=CG-ST)", ncg, NUMBER, grpFleuriet},
	}
}
metricsList returns the sequence to be printed after the financial statements
package reports
import (
	"database/sql"
	"fmt"
	"os"
	"os/signal"
	"path"
	"strconv"
	"strings"
	"github.com/360EntSecGroup-Skylar/excelize"
	"github.com/dude333/rapina/fetch"
	p "github.com/dude333/rapina/parsers"
	"github.com/pkg/errors"
)
const sectorAverage = "MÉDIA DO SETOR"
const (
	grpAccts int = iota + 100
	grpShares
	grpExtra
	grpFleuriet
)
type metric struct {
	descr  string
	val    float32
	format int  
	group  int  
}
type Report struct {
	average [][]float32
	groups map[int]bool
	printSector bool
	fetchStock *fetch.Stock
	cid  int     
	cnpj string  
	code string  
	db       *sql.DB  
	company  string   
	filename string   
	yamlFile string   
}
func New(parms map[string]interface{}) (*Report, error) {
	var r Report
	if v, ok := parms["db"]; ok {
		r.db = v.(*sql.DB)
	}
	if v, ok := parms["company"]; ok {
		r.company = v.(string)
	}
	if v, ok := parms["filename"]; ok {
		r.filename = v.(string)
	}
	if v, ok := parms["yamlFile"]; ok {
		r.yamlFile = v.(string)
	}
	if v, ok := parms["reports"]; ok {
		p := v.(map[string]bool)
		r.groups = make(map[int]bool, 4)
		r.groups[grpAccts] = true
		r.groups[grpShares] = p["ShowShares"]
		r.groups[grpExtra] = p["ExtraRatios"]
		r.groups[grpFleuriet] = p["Fleuriet"]
		r.printSector = true
		if v, ok := p["PrintSector"]; ok {
			r.printSector = v
		}
	}
	dataDir := path.Join(".", "data")
	if v, ok := parms["dataDir"]; ok {
		dataDir = v.(string)
	}
	apiKey := ""
	if v, ok := parms["apiKey"]; ok {
		apiKey = v.(string)
	}
	var err error
	log := NewLogger(os.Stderr)
	r.fetchStock, err = fetch.NewStock(r.db, log, apiKey, dataDir)
	return &r, err
}
func ReportToXlsx(parms map[string]interface{}) error {
	r, err := New(parms)
	if err != nil {
		return err
	}
	err = r.setCompany(r.company)
	if err != nil {
		return fmt.Errorf("empresa '%s' não encontrada no banco de dados", r.company)
	}
	e := newExcel()
	sheet, _ := e.newSheet(r.company)
	sheet.mergeCell("A1", "B1")
	sheet.print("A1", &[]string{r.company}, LEFT, true)
	accounts, _ := r.accountsItems(r.cid)
	baseItems, lastStatementsRow, lastMetricsRow := r.printCodesAndDescriptions(sheet, accounts, 'A', 2)
	begin, end, err := timeRange(r.db)
	if err != nil {
		return err
	}
	var values map[uint32]float32
	for y := begin; y <= end; y++ {
		row := 2
		col := colLetter(2 + y - begin)  
		cell := col + "1"
		title := "[" + strconv.Itoa(y) + "]"
		lastYear, isTTM, err := r.lastYear(r.cid)
		if lastYear == y && isTTM && err == nil {
			title = "[TTM/" + strconv.Itoa(y) + "]"
		}
		values, err = r.accountsValues(y)
		if err != nil {
			fmt.Println("[x]", err)
			continue
		}
		if y == end && sum(values) == 0 {
			end--
			break
		}
		_ = sheet.printTitle(cell, title)  
		for _, acct := range accounts {
			cell := col + strconv.Itoa(row)
			_ = sheet.printValue(cell, values[acct.code], NUMBER, baseItems[row])
			row++
		}
		row++
		cell = col + strconv.Itoa(row)
		_ = sheet.printTitle(cell, title)  
		row++
		for _, metric := range metricsList(values) {
			if !r.groups[metric.group] {
				continue
			}
			if metric.format != EMPTY {
				cell := col + strconv.Itoa(row)
				_ = sheet.printValue(cell, metric.val, metric.format, false)
			}
			row++
		}
	}  
	wide := (end - begin)
	year := begin
	top := 2
	bottom := top
	for col := 2; col <= 2+wide; col++ {
		vCol := col + wide + 2                                       
		_ = sheet.printTitle(axis(vCol, 1), "'"+strconv.Itoa(year))  
		year++
		var ref string
		for row := top; row <= lastStatementsRow; row++ {
			idx := row - top
			if idx < 0 || idx >= len(accounts) {
				break
			}
			if len(accounts[idx].cdConta) == 0 {
				break
			}
			n, _ := strconv.Atoi(accounts[idx].cdConta[:1])
			if n > 3 {
				break
			}
			switch accounts[idx].cdConta {
			case "1", "2", "3.01":
				ref = axis(col, row)
			}
			val := axis(col, row)
			formula := fmt.Sprintf(`=IfError(%s/%s, "-")`, val, ref)
			_ = sheet.printFormula(axis(vCol, row), formula, PERCENT, baseItems[row])
			bottom = row
		}
	}
	sheet.mergeCell(axis(1+wide+2, top), axis(1+wide+2, bottom))
	format := newFormat(DEFAULT, RIGHT, true)
	format.Alignment.Vertical = "top"
	format.Alignment.TextRotation = 90
	rotatedTextStyle := format.newStyle(sheet.xlsx)
	sheet.printCell(top, 1+wide+2, "ANÁLISE  VERTICAL", rotatedTextStyle)
	wide = (end - begin)
	year = begin
	top = lastStatementsRow + 2
	bottom = lastMetricsRow
	for col := 0; col <= wide-1; col++ {
		year++
		vCol := (2 + wide + 2) + col                                   
		_ = sheet.printTitle(axis(vCol, top), "'"+strconv.Itoa(year))  
		for row := top + 1; row <= bottom; row++ {
			vt0 := axis(col+2, row)
			vtn := axis(col+3, row)
			formula := fmt.Sprintf(`=IF(OR(%s="", %s=""), "", IF(MIN(%s, %s)<=0, IF((%s - %s)>0, "      ⇧", "      ⇩"), (%s/%s)-1))`,
				vtn, vt0, vtn, vt0, vtn, vt0, vtn, vt0)
			_ = sheet.printFormula(axis(vCol, row), formula, PERCENT, false)
		}
	}
	sheet.mergeCell(axis(2+wide+1, top+1), axis(2+wide+1, bottom))
	sheet.printCell(top+1, 1+wide+2, "ANÁLISE  HORIZONTAL", rotatedTextStyle)
	vCol := (2 + wide + 2) + wide + 1
	_ = sheet.printTitle(axis(vCol, top), "CAGR")
	for row := top + 1; row <= bottom; row++ {
		vt0 := axis(2, row)
		vtn := axis(2+wide, row)
		formula := fmt.Sprintf(`=IF(OR(%s="", %s="", %s=0, (%s*%s)<0), "", (%s/%s)^(1/%d)-1)`,
			vtn, vt0, vt0, vt0, vtn, vtn, vt0, wide)
		_ = sheet.printFormula(axis(vCol, row), formula, PERCENT, false)
	}
	sheet.autoWidth()
	if r.printSector {
		sheet2, err := e.newSheet("SETOR")
		if err == nil {
			_ = sheet2.xlsx.SetSheetViewOptions(sheet2.name, 0,
				excelize.ShowGridLines(false),
				excelize.ZoomScale(80),
			)
			_ = r.sectorReport(sheet2, r.company)
		}
	}
	err = e.saveAndCloseExcel(r.filename)
	if err == nil {
		fmt.Printf("[√] Dados salvos em %s\n", r.filename)
	}
	return err
}
func (r Report) sectorReport(sheet *Sheet, company string) (err error) {
	var interrupt bool
	c := make(chan os.Signal, 1)
	signal.Notify(c, os.Interrupt)
	go func() {
		<-c
		fmt.Println("\n[ ] Processamento interrompido")
		interrupt = true
	}()
	companies, secName, err := r.fromSector(company)
	if len(companies) <= 1 || err != nil {
		err = errors.Wrap(err, "erro ao ler arquivo de setores "+r.yamlFile)
		return
	}
	companies = append([]string{sectorAverage}, companies...)
	fmt.Println("[i] Criando relatório setorial (Ctrl+C para interromper)")
	var top, row, col int = 2, 0, 0
	var count int
	for _, co := range companies {
		row = top
		col++
		fmt.Printf("[ ] - %s", co)
		avg := false
		if co == sectorAverage {
			avg = true
			co = company
		}
		empty, err := r.companySummary(sheet, &row, &col, co, secName, count%3 == 0, avg)
		ok := "√"
		if err != nil || empty {
			ok = "x"
			col--
		} else {
			count++
			if count%3 == 0 {
				top = row + 2
				col = 0
			}
		}
		if interrupt {
			return nil
		}
		fmt.Printf("\r[%s\n", ok)
	}
	sheet.setColWidth(0, 2)
	return
}
func (r *Report) companySummary(sheet *Sheet, row, col *int, _company, sectorName string, printDescr, sectorAvg bool) (empty bool, err error) {
	err = r.setCompany(_company)
	if err != nil {
		err = errors.Errorf("empresa '%s' não encontrada no banco de dados", _company)
		return
	}
	begin, end, err := timeRange(r.db)
	if err != nil {
		return
	}
	sTitle := newFormat(DEFAULT, RIGHT, true).newStyle(sheet.xlsx)
	fCompanyName := newFormat(DEFAULT, CENTER, true)
	fCompanyName.size(16)
	sCompanyName := fCompanyName.newStyle(sheet.xlsx)
	fSectorName := newFormat(DEFAULT, LEFT, false)
	fSectorName.size(14)
	sSectorName := fSectorName.newStyle(sheet.xlsx)
	fDescr := newFormat(DEFAULT, RIGHT, false)
	fDescr.Border = []formatBorder{{Type: "left", Color: "333333", Style: 1}}
	sDescr := fDescr.newStyle(sheet.xlsx)
	fDescr.Border = []formatBorder{
		{Type: "top", Color: "333333", Style: 1},
		{Type: "left", Color: "333333", Style: 1},
	}
	sDescrTop := fDescr.newStyle(sheet.xlsx)
	fDescr.Border = []formatBorder{
		{Type: "top", Color: "333333", Style: 1},
	}
	sDescrBottom := fDescr.newStyle(sheet.xlsx)
	if printDescr {
		*col++
	}
	sheet.mergeCell(axis(*col, *row), axis(*col+end-begin+1, *row))
	if sectorAvg {
		sheet.printCell(*row-1, *col-1, sectorName, sSectorName)
		sheet.printCell(*row, *col, sectorAverage, sCompanyName)
	} else {
		sheet.printCell(*row, *col, _company, sCompanyName)
	}
	if printDescr {
		*col--
	}
	*row++
	rw := *row
	if printDescr {
		sheet.setColWidth(*col, 18)
		*col++
	}
	for y := begin; y <= end; y++ {
		var values map[uint32]float32
		var err error
		if sectorAvg {
			values, err = r.accountsAverage(_company, y)
			r.average = append(r.average, []float32{})
		} else {
			values, err = r.accountsValues(y)
		}
		if err != nil {
			fmt.Printf(" -- %v", err)
			return false, err
		}
		if y == end && sum(values) == 0 {
			end--
			break
		}
		*row = rw
		sheet.printCell(*row, *col, "["+strconv.Itoa(y)+"]", sTitle)
		*row++
		i := 0
		for _, metric := range metricsList(values) {
			if !r.groups[metric.group] {
				continue
			}
			if sectorAvg {
				r.average[y-begin] = append(r.average[y-begin], metric.val)
			}
			if printDescr {
				stl := sDescr
				if i == 0 {
					stl = sDescrTop
				}
				sheet.printCell(*row, *col-1, metric.descr, stl)
			}
			if metric.format != EMPTY {
				fVal := newFormat(metric.format, DEFAULT, false)
				fVal.Border = []formatBorder{
					{Type: "top", Color: "cccccc", Style: 1},
					{Type: "right", Color: "cccccc", Style: 1},
					{Type: "bottom", Color: "cccccc", Style: 1},
					{Type: "left", Color: "cccccc", Style: 1},
				}
				if len(r.average) > 0 && len(r.average[y-begin]) > 0 && len(r.average[y-begin]) >= i {
					f := formatFill{Type: "pattern", Pattern: 1}
					if metric.val > r.average[y-begin][i] {
						f.Color = []string{"c6efce"}  
						fVal.Fill = f
					} else if metric.val < r.average[y-begin][i] {
						f.Color = []string{"ffc7ce"}  
						fVal.Fill = f
					}
				}
				stl := fVal.newStyle(sheet.xlsx)
				sheet.printCell(*row, *col, metric.val, stl)
			}
			*row++
			i++
		}
		if printDescr {
			sheet.printCell(*row, *col-1, "", sDescrBottom)
		}
		printDescr = false
		*col++
	}  
	bottom := *row
	wide := end - begin
	_ = sheet.printTitle(axis(*col, rw), "CAGR")
	for r := rw + 1; r <= bottom; r++ {
		vt0 := axis(*col-wide-1, r)
		vtn := axis(*col-1, r)
		formula := fmt.Sprintf(`=IF(OR(%s="", %s="", %s=0, (%s*%s)<0), "", (%s/%s)^(1/%d)-1)`,
			vtn, vt0, vt0, vt0, vtn, vtn, vt0, wide)
		_ = sheet.printFormula(axis(*col, r), formula, PERCENT, false)
	}
	*col++
	return
}
func (r *Report) Summary(company string) (map[string]string, error) {
	m := make(map[string]string)
	err := r.setCompany(company)
	if err != nil {
		return m, err
	}
	values, err := r.accountsValues(2020)
	if err != nil {
		return m, err
	}
	for _, metric := range metricsList(values) {
		if !r.groups[metric.group] {
			continue
		}
	}
	return m, nil
}