fixing a bug in ExtractDBDatamap and still working on data import code

6 files changed, 52 insertions, 4 deletions

diff --git a/pkg/datamaps/db.go b/pkg/datamaps/db.go
index f1204a9..0b6f588 100644
--- a/pkg/datamaps/db.go
+++ b/pkg/datamaps/db.go
@@ -44,10 +44,14 @@ func setupDB(path string) (*sql.DB, error) {
 				 CREATE TABLE return_data(
 					 id INTEGER PRIMARY KEY,
 					 dml_id INTEGER,
+					 ret_id INTEGER,
 					 value TEXT,
 					 FOREIGN KEY (dml_id)
 					 REFERENCES datamap_line(id) 
 					 ON DELETE CASCADE
+					 FOREIGN KEY (ret_id)
+					 REFERENCES return(id) 
+					 ON DELETE CASCADE
 				 );
 				 `
 	os.Create(path)
@@ -162,6 +166,7 @@ func DatamapToDB(opts *Options) error {
 }
 
 func importXLSXtoDB(dm_name string, return_name string, file string, db *sql.DB) error {
+	// d, err := ExtractDBDatamap(dm_name, file, db)
 	d, err := ExtractDBDatamap(dm_name, file, db)
 	if err != nil {
 		return err
@@ -181,7 +186,8 @@ func importXLSXtoDB(dm_name string, return_name string, file string, db *sql.DB)
 		log.Fatal(err)
 	}
 
-	rId, err := res.LastInsertId()
+	retId, err := res.LastInsertId()
+	fmt.Println(retId)
 	if err != nil {
 		log.Fatal(err)
 	}
@@ -192,10 +198,41 @@ func importXLSXtoDB(dm_name string, return_name string, file string, db *sql.DB)
 		return err
 	}
 
-	stmtValues, err := tx.Prepare("INSERT INTO return_data (name, date_created) VALUES(?,?)")
+	for sheetName, sheetData := range d {
+
+		for cellRef, cellData := range sheetData {
+			// fmt.Printf("Getting %s from sheet %s\n", cellRef, sheetName)
+
+			dmlQuery, err := db.Prepare("select id from datamap_line where (sheet=? and cellref=?)")
+			if err != nil {
+				log.Fatal(err)
+			}
+			defer dmlQuery.Close()
+			dmlIdRow := dmlQuery.QueryRow(sheetName, cellRef)
+			fmt.Println(dmlIdRow)
+
+			var dmlId *int
+
+			if err := dmlIdRow.Scan(&dmlId); err != nil {
+				log.Fatal(err)
+			}
+
+			insertStmt, err := db.Prepare("insert into return_data (dml_id, value) values(?,?)")
+			if err != nil {
+				log.Fatal(err)
+			}
+			defer insertStmt.Close()
+
+			res, err = insertStmt.Exec(dmlId, cellData.Value)
+			if err != nil {
+				log.Fatal(err)
+			}
+		}
+	}
+
+	err = tx.Commit()
 	if err != nil {
 		return err
 	}
-
 	return nil
 }
diff --git a/pkg/datamaps/reader.go b/pkg/datamaps/reader.go
index d706d8f..4f5e1a0 100644
--- a/pkg/datamaps/reader.go
+++ b/pkg/datamaps/reader.go
@@ -200,10 +200,16 @@ func ExtractDBDatamap(name string, file string, db *sql.DB) (extractedData, erro
 
 	names := getSheetNames(ddata)
 	outer := make(extractedData, len(names))
-	inner := make(map[string]xlsx.Cell)
+	var inner map[string]xlsx.Cell
+
+	seen := make(map[string]bool)
 
 	for _, i := range ddata {
 		sheet := i.Sheet
+		if !seen[sheet] {
+			seen[sheet] = true
+			inner = make(map[string]xlsx.Cell)
+		}
 		cellref := i.Cellref
 
 		if val, ok := xdata[sheet][cellref]; ok {
diff --git a/pkg/datamaps/testdata/datamap_matches_test_template.csv b/pkg/datamaps/testdata/datamap_matches_test_template.csv
new file mode 100644
index 0000000..553828d
--- /dev/null
+++ b/pkg/datamaps/testdata/datamap_matches_test_template.csv
@@ -0,0 +1,5 @@
+cell_key,template_sheet,cellreference
+A Ten,Introduction,A1
+A Test,Introduction,C9
+A Vunt,Introduction,C22
+Floaty,Another Sheet,D5
diff --git a/pkg/datamaps/testdata/test_template.xlsx b/pkg/datamaps/testdata/test_template.xlsx
deleted file mode 100644
index 77ac56f..0000000
--- a/pkg/datamaps/testdata/test_template.xlsx
+++ /dev/null
diff --git a/pkg/datamaps/testdata/test_template2.xlsx b/pkg/datamaps/testdata/test_template2.xlsx
deleted file mode 100644
index 77ac56f..0000000
--- a/pkg/datamaps/testdata/test_template2.xlsx
+++ /dev/null
diff --git a/pkg/datamaps/testdata/test_template3.xlsx b/pkg/datamaps/testdata/test_template3.xlsx
deleted file mode 100644
index 77ac56f..0000000
--- a/pkg/datamaps/testdata/test_template3.xlsx
+++ /dev/null