1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
package datamaps
import (
"database/sql"
"log"
"path/filepath"
"github.com/tealeg/xlsx/v3"
// Needed for the sqlite3 driver
_ "github.com/mattn/go-sqlite3"
)
func ExportMaster(opts *Options) error {
// A master represents a set of file data from the database. Actually, in terms of the database,
// it should represent a "return".
//
// The meat of the master is of the format:
// Key 1 | Key_1_Value_for_FD_1 | Key_1_Value_for_FD_2 | Key_1_Value_for_FD_3 | ... etc
// Key 2 | Key_2_Value_for_FD_1 | Key_2_Value_for_FD_2 | Key_2_Value_for_FD_3 | ... etc
// Key 3 | Key_3_Value_for_FD_1 | Key_3_Value_for_FD_2 | Key_3_Value_for_FD_3 | ... etc
// ...
// Could be represented as a slice or a map[string][]string
// SQL statement:
//
// SELECT datamap_line.key, return_data.value, return_data.filename
// FROM (((return_data
// INNER JOIN datamap_line ON return_data.dml_id=datamap_line.id)
// INNER JOIN datamap ON datamap_line.dm_id=datamap.id)
// INNER JOIN return on return_data.ret_id=return.id)
// WHERE datamap.name="Tonk 1" AND return.name="Hunkers";
// filename := filepath.Join(opts.MasterOutPutPath, "master.xlsx")
// a test key
targetKey := "A Rabbit"
wb := xlsx.NewFile()
sh, err := wb.AddSheet("Master Data")
// SQLITE CODE
db, err := sql.Open("sqlite3", opts.DBPath)
// Get number amount of datamap keys in target datamap
keyCountRows := db.QueryRow("SELECT count(key) FROM datamap_line, datamap WHERE datamap.name=?;", opts.DMName)
if err != nil {
log.Fatal(err)
}
var datamapKeysNumber int64
if err := keyCountRows.Scan(&datamapKeysNumber); err != nil {
return err
}
datamapKeysRows, err := db.Query("SELECT key FROM datamap_line, datamap WHERE datamap.name=?;", opts.DMName)
if err != nil {
log.Fatal(err)
}
var datamapKeys []string
var i int64
for i = 0; i < datamapKeysNumber; i++ {
if k := datamapKeysRows.Next(); k {
var key string
if err := datamapKeysRows.Scan(&key); err != nil {
return err
}
datamapKeys = append(datamapKeys, key)
}
}
sqlCount := `SELECT count(return_data.id)
FROM (((return_data
INNER JOIN datamap_line ON return_data.dml_id=datamap_line.id)
INNER JOIN datamap ON datamap_line.dm_id=datamap.id)
INNER JOIN return on return_data.ret_id=return.id)
WHERE datamap.name=? AND return.name=? AND datamap_line.key=?
GROUP BY datamap_line.key;`
var rowCount int64
rowCountRes := db.QueryRow(sqlCount, opts.DMName, opts.ReturnName, targetKey)
if err != nil {
return err
}
if err := rowCountRes.Scan(&rowCount); err != nil {
return err
}
getDataSQL := `SELECT datamap_line.key, return_data.value, return_data.filename
FROM (((return_data
INNER JOIN datamap_line ON return_data.dml_id=datamap_line.id)
INNER JOIN datamap ON datamap_line.dm_id=datamap.id)
INNER JOIN return on return_data.ret_id=return.id)
WHERE datamap.name=? AND return.name=? AND datamap_line.key=?;`
var values = make(map[string][]string)
for _, k := range datamapKeys {
masterData, err := db.Query(getDataSQL, opts.DMName, opts.ReturnName, k)
if err != nil {
return err
}
var x int64
for x = 0; x < rowCount; x++ {
var key, filename, value string
if b := masterData.Next(); b {
if err := masterData.Scan(&key, &value, &filename); err != nil {
return err
}
values = appendValueMap(key, value, values)
}
}
}
var masterRow int64
for masterRow = 1; masterRow <= datamapKeysNumber; masterRow++ {
r, err := sh.Row(int(masterRow))
if err != nil {
log.Fatal(err)
}
dmlKey := datamapKeys[masterRow-1]
if sl := r.WriteSlice(append([]string{dmlKey}, values[dmlKey]...), -1); sl == -1 {
log.Printf("not a slice type")
}
log.Printf("writing slice to row\n")
}
log.Printf("saving master at %s", opts.MasterOutPutPath)
if err := wb.Save(filepath.Join(opts.MasterOutPutPath, "master.xlsx")); err != nil {
log.Fatalf("cannot save file to %s", opts.MasterOutPutPath)
}
sh.Close()
return nil
}
func appendValueMap(k, v string, values map[string][]string) map[string][]string {
var keyIn bool
for kv, _ := range values {
if kv == k {
keyIn = true
break
}
}
if keyIn {
slice, ok := values[k]
if !ok {
log.Fatalf("%s is not a key in this map", k)
}
slice = append(slice, v)
values[k] = slice
return values
} else {
values[k] = []string{v}
return values
}
}
|
