1 files changed, 267 insertions, 0 deletions
diff --git a/datamaps/db.go b/datamaps/db.go
new file mode 100644
index 0000000..296b1b2
--- /dev/null
+++ b/datamaps/db.go
@@ -0,0 +1,267 @@
+package datamaps
+
+import (
+	"database/sql"
+	"errors"
+	"fmt"
+	"log"
+	"os"
+	"path"
+	"time"
+
+	// Needed for the sqlite3 driver
+	_ "github.com/mattn/go-sqlite3"
+)
+
+// setupDB creates the intitial database
+func setupDB(path string) (*sql.DB, error) {
+	stmtBase := `DROP TABLE IF EXISTS datamap;
+				 DROP TABLE IF EXISTS datamap_line;
+				 DROP TABLE IF EXISTS return;
+				 DROP TABLE IF EXISTS return_data;
+
+				  CREATE TABLE datamap(
+					  id INTEGER PRIMARY KEY,
+					  name TEXT,
+					  date_created TEXT);
+
+				  CREATE TABLE datamap_line(
+					id INTEGER PRIMARY KEY,   
+					dm_id INTEGER,            
+					key TEXT NOT NULL,        
+					sheet TEXT NOT NULL,      
+					cellref TEXT,             
+					FOREIGN KEY (dm_id)       
+					REFERENCES datamap(id) 
+					ON DELETE CASCADE      
+				  );                        
+
+				 CREATE TABLE return(
+					 id INTEGER PRIMARY KEY,
+					 name TEXT,
+					 date_created TEXT
+					);
+
+				 CREATE TABLE return_data(
+					 id INTEGER PRIMARY KEY,
+					 dml_id INTEGER,
+					 ret_id INTEGER,
+					 filename TEXT,
+					 value TEXT,
+					 numfmt TEXT,
+					 vFormatted TEXT,
+					 FOREIGN KEY (dml_id)
+					 REFERENCES datamap_line(id) 
+					 ON DELETE CASCADE
+					 FOREIGN KEY (ret_id)
+					 REFERENCES return(id) 
+					 ON DELETE CASCADE
+				 );
+				 `
+	if _, err := os.Create(path); err != nil {
+		return nil, err
+	}
+	db, err := sql.Open("sqlite3", path)
+	if err != nil {
+		return db, errors.New("Cannot open that damn database file")
+	}
+
+	// We probably don't need pragma here but we have it for later.
+	pragma := "PRAGMA foreign_keys = ON;"
+	_, err = db.Exec(pragma)
+	if err != nil {
+		// log.Printf("%q: %s\n", err, pragma)
+		return nil, err
+	}
+
+	_, err = db.Exec(stmtBase)
+	if err != nil {
+		// log.Printf("%q: %s\n", err, stmt_base)
+		return nil, err
+	}
+
+	return db, nil
+}
+
+// ImportToDB imports a directory of xlsx files to the database, using the datamap
+// to filter the data.
+func ImportToDB(opts *Options) error {
+	log.Printf("Importing files in %s as return named %s using datamap named %s.", opts.XLSXPath, opts.ReturnName, opts.DMName)
+
+	target, err := getTargetFiles(opts.XLSXPath)
+	if err != nil {
+		return err
+	}
+
+	db, err := sql.Open("sqlite3", opts.DBPath)
+	if err != nil {
+		return err
+	}
+
+	for _, vv := range target {
+		// TODO: Do the work!
+
+		if err := importXLSXtoDB(opts.DMName, opts.ReturnName, vv, db); err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+// DatamapToDB takes a slice of datamapLine and writes it to a sqlite3 db file.
+func DatamapToDB(opts *Options) error {
+	log.Printf("Importing datamap file %s and naming it %s.\n", opts.DMPath, opts.DMName)
+
+	data, err := ReadDML(opts.DMPath)
+	if err != nil {
+		log.Fatal(err)
+	}
+
+	d, err := sql.Open("sqlite3", opts.DBPath)
+	if err != nil {
+		return errors.New("Cannot open that damn database file")
+	}
+
+	tx, err := d.Begin()
+	if err != nil {
+		return err
+	}
+
+	pragma := "PRAGMA foreign_keys = ON;"
+	_, err = d.Exec(pragma)
+	if err != nil {
+		log.Printf("%q: %s\n", err, pragma)
+		return err
+	}
+
+	stmtDm, err := tx.Prepare("INSERT INTO datamap (name, date_created) VALUES(?,?)")
+	if err != nil {
+		return err
+	}
+
+	res, err := stmtDm.Exec(opts.DMName, time.Now())
+	if err != nil {
+		return err
+	}
+
+	lastID, err := res.LastInsertId()
+	if err != nil {
+		return err
+	}
+
+	stmtDml, err := tx.Prepare("INSERT INTO datamap_line (dm_id, key, sheet, cellref) VALUES(?,?,?,?);")
+	if err != nil {
+		return err
+	}
+
+	defer stmtDm.Close()
+	defer stmtDml.Close()
+
+	for _, dml := range data {
+		_, err = stmtDml.Exec(lastID, dml.Key, dml.Sheet, dml.Cellref)
+		if err != nil {
+			return err
+		}
+	}
+	err = tx.Commit()
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
+
+func importXLSXtoDB(dmName string, returnName string, file string, db *sql.DB) error {
+	// d, err := ExtractDBDatamap(dmName, file, db)
+	_, filename := path.Split(file)
+	d, err := ExtractDBDatamap(dmName, file, db)
+	if err != nil {
+		return err
+	}
+	log.Printf("Extracting from %s.\n", file)
+
+	// If there is already a return with a matching name, use that.
+	rtnQuery, err := db.Prepare("select id from return where (return.name=?)")
+	if err != nil {
+		fmt.Errorf("cannot create a query to get the return - %v", err)
+	}
+	defer rtnQuery.Close()
+
+	var retID int64
+	err = rtnQuery.QueryRow(returnName).Scan(&retID)
+	if err != nil {
+		log.Printf("Couldn't find an existing return named '%s' so let's create it.\n", returnName)
+	}
+
+	if retID == 0 {
+		stmtReturn, err := db.Prepare("insert into return(name, date_created) values(?,?)")
+		if err != nil {
+			fmt.Errorf("cannot prepare a statement to create a new return - %v", err)
+		}
+		defer stmtReturn.Close()
+
+		res, err := stmtReturn.Exec(returnName, time.Now())
+		if err != nil {
+			err := fmt.Errorf("%v\nCannot create %s", err, returnName)
+			log.Println(err.Error())
+			os.Exit(1)
+		}
+
+		retID, err = res.LastInsertId()
+		if err != nil {
+			fmt.Errorf("cannot get id of return - %v", err)
+		}
+	}
+
+	// We're going to need a transaction for the big stuff
+	tx, err := db.Begin()
+	if err != nil {
+		fmt.Errorf("cannot start a database transaction - %v", err)
+	}
+
+	for sheetName, sheetData := range d {
+
+		for cellRef, cellData := range sheetData {
+
+			dmlQuery, err := db.Prepare("select id from datamap_line where (sheet=? and cellref=?)")
+			if err != nil {
+				fmt.Errorf("cannot prepare a statement to get the datamap line - %v", err)
+			}
+			defer dmlQuery.Close()
+			dmlIDRow := dmlQuery.QueryRow(sheetName, cellRef)
+
+			var dmlID *int
+
+			if err := dmlIDRow.Scan(&dmlID); err != nil {
+				err := fmt.Errorf("cannot find a datamap_line row for %s and %s: %s", sheetName, cellRef, err)
+				log.Println(err.Error())
+			}
+
+			insertStmt, err := db.Prepare("insert into return_data (dml_id, ret_id, filename, value, numfmt, vFormatted) values(?,?,?,?,?,?)")
+			if err != nil {
+				fmt.Errorf("cannot insert row into return_data - %v", err)
+			}
+			defer insertStmt.Close()
+
+			// Hack to fix bug in Libreoffice numformats for dates
+			if cellData.NumFmt == "DD/MM/YY" {
+				cellData.SetFormat("dd/mm/yy")
+			}
+			fValue, err := cellData.FormattedValue()
+			if err != nil {
+				fmt.Errorf("cannot get the formatted value for %#v, %v", cellData, err)
+			}
+
+			_, err = insertStmt.Exec(dmlID, retID, filename, cellData.Value, cellData.NumFmt, fValue)
+			if err != nil {
+				fmt.Errorf("cannot execute statement to insert return data - %v", err)
+			}
+		}
+	}
+
+	err = tx.Commit()
+	if err != nil {
+		return err
+	}
+	return nil
+}