You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
507 lines
14 KiB
507 lines
14 KiB
package database |
|
|
|
import ( |
|
"database/sql" |
|
"log" |
|
"spargcom/senuma/domain" |
|
|
|
_ "github.com/go-sql-driver/mysql" |
|
) |
|
|
|
var ( |
|
dbConnection = "gosenuma:hurtz@tcp(10.1.1.6:3300)/senuma" |
|
dbType = "mysql" |
|
) |
|
|
|
// Artikel |
|
|
|
func CreateArtikel(art domain.Artikel) (resArt domain.Artikel, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("Verbindung hergestellt") |
|
log.Println("Bezeichnung = ", art.Bezeichnung) |
|
defer conn.Close() |
|
|
|
res, err := conn.Exec("INSERT INTO artikel VALUES(?,?,?)", art.Id, art.Bezeichnung, art.ArtNrExt) |
|
if err != nil { |
|
log.Println("Error while executing insert statement", err) |
|
} |
|
lastId, err := res.LastInsertId() |
|
art.Id = uint64(lastId) |
|
resArt = art |
|
|
|
return resArt, err |
|
} |
|
|
|
func DeleteArtikel(artId uint64) (result, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
_, err = conn.Exec("DELETE FROM artikel WHERE id = ?", artId) |
|
if err != nil { |
|
log.Fatal("Error while executing DELETE statement", err) |
|
} |
|
return result, err |
|
} |
|
|
|
func ShowArtikel() (artArray []domain.Artikel, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
results, err := conn.Query("SELECT * FROM artikel") |
|
if err != nil { |
|
log.Fatal("Error while executing SELECt statement", err) |
|
} |
|
|
|
var art domain.Artikel |
|
for results.Next() { |
|
err = results.Scan(&art.Id, &art.Bezeichnung, &art.ArtNrExt) |
|
if err != nil { |
|
log.Fatal("Error: ", err) |
|
} |
|
artArray = append(artArray, art) |
|
} |
|
return artArray, err |
|
} |
|
|
|
func GetArtikel(id uint64) (artikel domain.Artikel, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
results, err := conn.Query("SELECT * FROM artikel WHERE id=?", id) |
|
if err != nil { |
|
log.Fatal("Error while executing SELECt statement", err) |
|
} |
|
for results.Next() { |
|
err = results.Scan(&artikel.Id, &artikel.Bezeichnung, &artikel.ArtNrExt) |
|
if err != nil { |
|
log.Fatal("Error: ", err) |
|
} |
|
} |
|
|
|
return artikel, err |
|
} |
|
|
|
func UpdateArtikel(art domain.Artikel) (resArt domain.Artikel, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("Verbindung hergestellt") |
|
log.Println("Bezeichnung = ", art.Bezeichnung) |
|
defer conn.Close() |
|
|
|
_, err = conn.Exec("UPDATE artikel SET bezeichnung=?, artnr_ext=? WHERE id=?", art.Bezeichnung, art.ArtNrExt, art.Id) |
|
if err != nil { |
|
log.Println("Error while executing insert statement", err) |
|
} |
|
|
|
resArt = art |
|
|
|
return resArt, err |
|
} |
|
|
|
// Kunde |
|
|
|
func CreateKunde(kd domain.Kunde) (resKd domain.Kunde, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("Verbindung hergestellt") |
|
log.Println("Name = ", kd.Name) |
|
defer conn.Close() |
|
|
|
res, err := conn.Exec("INSERT INTO kunden VALUES(?,?,?)", kd.Id, kd.Name, kd.KdNrExt) |
|
if err != nil { |
|
log.Println("Error while executing insert statement", err) |
|
} |
|
lastId, err := res.LastInsertId() |
|
kd.Id = uint(lastId) |
|
resKd = kd |
|
|
|
return resKd, err |
|
} |
|
|
|
func DeleteKunde(kdId uint) (result, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
_, err = conn.Exec("DELETE FROM kunden WHERE id = ?", kdId) |
|
if err != nil { |
|
log.Fatal("Error while executing DELETE statement", err) |
|
} |
|
return result, err |
|
} |
|
|
|
func ShowKunde() (kdArray []domain.Kunde, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
results, err := conn.Query("SELECT * FROM kunden") |
|
if err != nil { |
|
log.Fatal("Error while executing SELECt statement", err) |
|
} |
|
|
|
var kd domain.Kunde |
|
for results.Next() { |
|
err = results.Scan(&kd.Id, &kd.Name, &kd.KdNrExt) |
|
if err != nil { |
|
log.Fatal("Error: ", err) |
|
} |
|
kdArray = append(kdArray, kd) |
|
} |
|
return kdArray, err |
|
} |
|
|
|
func GetKunde(id uint) (kunde domain.Kunde, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
results, err := conn.Query("SELECT * FROM kunden WHERE id=?", id) |
|
if err != nil { |
|
log.Fatal("Error while executing SELECt statement", err) |
|
} |
|
for results.Next() { |
|
err = results.Scan(&kunde.Id, &kunde.Name, &kunde.KdNrExt) |
|
if err != nil { |
|
log.Fatal("Error: ", err) |
|
} |
|
} |
|
|
|
return kunde, err |
|
} |
|
|
|
func UpdateKunde(ku domain.Kunde) (resKunde domain.Kunde, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("Verbindung hergestellt") |
|
log.Println("Name = ", ku.Name) |
|
defer conn.Close() |
|
|
|
_, err = conn.Exec("UPDATE kunden SET name=?, kdnr_ext=? WHERE id=?", ku.Name, ku.KdNrExt, ku.Id) |
|
if err != nil { |
|
log.Println("Error while executing insert statement", err) |
|
} |
|
|
|
resKunde = ku |
|
|
|
return resKunde, err |
|
} |
|
|
|
// Lieferanten |
|
|
|
func CreateLieferant(lief domain.Lieferant) (resLief domain.Lieferant, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("Verbindung hergestellt") |
|
log.Println("Name = ", lief.Name) |
|
defer conn.Close() |
|
|
|
res, err := conn.Exec("INSERT INTO lieferanten VALUES(?,?,?)", lief.Id, lief.Name, lief.LiefNrExt) |
|
if err != nil { |
|
log.Println("Error while executing insert statement", err) |
|
} |
|
lastId, err := res.LastInsertId() |
|
lief.Id = uint(lastId) |
|
resLief = lief |
|
|
|
return resLief, err |
|
} |
|
|
|
func DeleteLieferant(liefId uint) (result, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
_, err = conn.Exec("DELETE FROM lieferanten WHERE id = ?", liefId) |
|
if err != nil { |
|
log.Fatal("Error while executing DELETE statement", err) |
|
} |
|
return result, err |
|
} |
|
|
|
func ShowLieferant() (liefArray []domain.Lieferant, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
results, err := conn.Query("SELECT * FROM lieferanten") |
|
if err != nil { |
|
log.Fatal("Error while executing SELECt statement", err) |
|
} |
|
|
|
var lief domain.Lieferant |
|
for results.Next() { |
|
err = results.Scan(&lief.Id, &lief.Name, &lief.LiefNrExt) |
|
if err != nil { |
|
log.Fatal("Error: ", err) |
|
} |
|
liefArray = append(liefArray, lief) |
|
} |
|
return liefArray, err |
|
} |
|
|
|
func GetLieferant(id uint) (lieferant domain.Lieferant, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
results, err := conn.Query("SELECT * FROM lieferanten WHERE id=?", id) |
|
if err != nil { |
|
log.Fatal("Error while executing SELECt statement", err) |
|
} |
|
for results.Next() { |
|
err = results.Scan(&lieferant.Id, &lieferant.Name, &lieferant.LiefNrExt) |
|
if err != nil { |
|
log.Fatal("Error: ", err) |
|
} |
|
} |
|
|
|
return lieferant, err |
|
} |
|
|
|
func UpdateLieferant(lief domain.Lieferant) (resLief domain.Lieferant, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("Verbindung hergestellt") |
|
log.Println("Name = ", lief.Name) |
|
defer conn.Close() |
|
|
|
_, err = conn.Exec("UPDATE lieferanten SET name=?, liefnr_ext=? WHERE id=?", lief.Name, lief.LiefNrExt, lief.Id) |
|
if err != nil { |
|
log.Println("Error while executing insert statement", err) |
|
} |
|
|
|
resLief = lief |
|
|
|
return resLief, err |
|
} |
|
|
|
// Seriennummern |
|
|
|
func CreateSerial(serial domain.Seriennummer) (resSerial domain.Seriennummer, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("Verbindung hergestellt") |
|
log.Println("Seriennummer = ", serial.SerienNr) |
|
defer conn.Close() |
|
|
|
res, err := conn.Exec("INSERT INTO seriennummer VALUES(?,?,?,?,?,?,?,?,?)", serial.Id, serial.ArtId, serial.KdId, serial.LiefId, serial.SerienNr, serial.EkDat, serial.VkDat, serial.GarantieBis, serial.Bemerkung) |
|
if err != nil { |
|
log.Println("Error while executing insert statement", err) |
|
} |
|
lastId, err := res.LastInsertId() |
|
serial.Id = uint64(lastId) |
|
resSerial = serial |
|
|
|
return resSerial, err |
|
} |
|
|
|
func DeleteSerial(serialId uint64) (result, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
_, err = conn.Exec("DELETE FROM seriennummer WHERE id = ?", serialId) |
|
if err != nil { |
|
log.Fatal("Error while executing DELETE statement", err) |
|
} |
|
return result, err |
|
} |
|
|
|
func ShowSerial() (serialArray []domain.Seriennummer, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
results, err := conn.Query("SELECT s.*, a.bezeichnung, k.name, l.name FROM seriennummer s, artikel a, kunden k, lieferanten l WHERE a.id = s.artid AND k.id = s.kdid AND l.id = s.liefid") |
|
if err != nil { |
|
log.Fatal("Error while executing SELECt statement", err) |
|
} |
|
|
|
var serial domain.Seriennummer |
|
for results.Next() { |
|
err = results.Scan(&serial.Id, &serial.ArtId, &serial.KdId, &serial.LiefId, &serial.SerienNr, &serial.EkDat, &serial.VkDat, &serial.GarantieBis, &serial.Bemerkung, &serial.ArtName, &serial.KdName, &serial.LiefName) |
|
if err != nil { |
|
log.Fatal("Error: ", err) |
|
} |
|
serialArray = append(serialArray, serial) |
|
} |
|
return serialArray, err |
|
} |
|
|
|
func GetSerial(id uint64) (serial domain.Seriennummer, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("DB Verbindung hergestellt") |
|
defer conn.Close() |
|
|
|
results, err := conn.Query("SELECT s.*, a.bezeichnung, k.name, l.name FROM seriennummer s, artikel a, kunden k, lieferanten l WHERE a.id = s.artid AND k.id = s.kdid AND l.id = s.liefid AND id=?", id) |
|
if err != nil { |
|
log.Fatal("Error while executing SELECt statement", err) |
|
} |
|
for results.Next() { |
|
err = results.Scan(&serial.Id, &serial.ArtId, &serial.KdId, &serial.LiefId, &serial.SerienNr, &serial.EkDat, &serial.VkDat, &serial.GarantieBis, &serial.Bemerkung, &serial.ArtName, &serial.KdName, &serial.LiefName) |
|
if err != nil { |
|
log.Fatal("Error: ", err) |
|
} |
|
} |
|
|
|
return serial, err |
|
} |
|
|
|
func UpdateSerial(serial domain.Seriennummer) (resSerial domain.Seriennummer, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
log.Println("Error while connecting DB: ", err) |
|
} |
|
log.Println("Verbindung hergestellt") |
|
log.Println("Seriennummer = ", serial.SerienNr) |
|
defer conn.Close() |
|
|
|
_, err = conn.Exec("UPDATE seriennummer SET artid=?, kdid=?, liefid=?, seriennr=?, ekdat=?, vkdat=?, garantiebis=?, bemerkung=? WHERE id=?", serial.ArtId, serial.KdId, serial.LiefId, serial.SerienNr, serial.EkDat, serial.VkDat, serial.GarantieBis, serial.Bemerkung, serial.Id) |
|
if err != nil { |
|
log.Println("Error while executing insert statement", err) |
|
} |
|
|
|
resSerial = serial |
|
|
|
return resSerial, err |
|
} |
|
|
|
/*func CreateUser(us domain.User) (resUs domain.User, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
fmt.Println("Error while connecting DB: ", err) |
|
} |
|
fmt.Println("Verbindung hergestellt") |
|
defer conn.Close() |
|
stmt, err := conn.Prepare("INSERT INTO `user` (`id`, `username`, `full_name`, `email`, `group_id`, `is_active`, `is_admin`, `password`) VALUES(?,?,?,?,?,?,?,?)") |
|
res, err := stmt.Exec(us.Id, us.Username, us.FullName, us.Email, us.GroupId, us.IsActive, us.IsAdmin, us.Password) |
|
if err != nil { |
|
fmt.Println("Error while executing insert statement", err) |
|
} |
|
lastId, err := res.LastInsertId() |
|
us.Id = uint(lastId) |
|
resUs = us |
|
|
|
return resUs, err |
|
} |
|
|
|
func CreateGroup(grp domain.Group) (resGrp domain.Group, err error) { |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
fmt.Println("Error while connecting DB: ", err) |
|
} |
|
fmt.Println("Verbindung hergestellt") |
|
defer conn.Close() |
|
stmt, err := conn.Prepare("INSERT INTO `group` (`id`, `name`, `booked_package`, `billing_address`, `auth_token`, `is_active`, `billing_user`) VALUES(?,?,?,?,?,?,?)") |
|
res, err := stmt.Exec(grp.Id, grp.Name, grp.BookedPackage, grp.BillingAddress, grp.AuthToken, grp.IsActive, grp.BillingUser) |
|
if err != nil { |
|
fmt.Println("Error while executing insert statement", err) |
|
} |
|
lastId, err := res.LastInsertId() |
|
grp.Id = uint(lastId) |
|
resGrp = grp |
|
|
|
return resGrp, err |
|
} |
|
|
|
func CreateAccount(us domain.User) (resAcc domain.Account, err error) { |
|
|
|
var acc domain.Account |
|
fmt.Println("User ID:", us.Id) |
|
fmt.Println("Group ID:", us.GroupId) |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
fmt.Println("Error while connecting DB: ", err) |
|
} |
|
fmt.Println("Verbindung hergestellt") |
|
defer conn.Close() |
|
stmt, err := conn.Prepare("INSERT INTO `account` (`id`, `group_id`, `created`, `is_active`) VALUES(?,?,?,?)") |
|
|
|
res, err := stmt.Exec(acc.Id, us.GroupId, time.Now(), acc.IsActive) |
|
if err != nil { |
|
fmt.Println("Error while executing insert statement", err) |
|
} |
|
lastId, err := res.LastInsertId() |
|
acc.Id = uint(lastId) |
|
resAcc = acc |
|
fmt.Println("Created") |
|
return resAcc, err |
|
|
|
} |
|
|
|
func CreateTunnel(tun domain.Tunnel) (resTun domain.Tunnel, err error) { |
|
fmt.Println("Adding tunnel to database...") |
|
conn, err := sql.Open(dbType, dbConnection) |
|
if err != nil { |
|
fmt.Println("Error while connecting DB: ", err) |
|
|
|
} |
|
fmt.Println("Verbindung hergestellt") |
|
defer conn.Close() |
|
stmt, err := conn.Prepare("INSERT INTO `tunnels` (`id`, `name`, `port`, `is_active`, `created`, `routed_name`, `routed_port`, `account_id`) VALUES(?,?,?,?,?,?,?,?)") |
|
|
|
res, err := stmt.Exec(tun.Id, tun.Name, tun.Port, tun.IsActive, time.Now(), tun.RoutedName, tun.RoutedPort, tun.AccountId) |
|
if err != nil { |
|
fmt.Println("Error while executing insert statement", err) |
|
} |
|
lastId, err := res.LastInsertId() |
|
tun.Id = uint64(lastId) |
|
resTun = tun |
|
fmt.Println("Created") |
|
return resTun, err |
|
}*/
|
|
|