func fetchItems(db *sqlite3.Conn, a *Account, sql string) ([]*Item, error) {
// find all the items for this account
results := make([]*Item, 0)
args := sqlite3.NamedArgs{"$a": a.Id}
row := make(sqlite3.RowMap)
for s, err := db.Query(sql, args); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
barcode, barcodeFound := row["barcode"]
desc, descFound := row["product_desc"]
ind, indFound := row["product_ind"]
since, sinceFound := row["strftime('%s', posted)"]
if barcodeFound {
result := new(Item)
result.Id = rowid
result.Barcode = barcode.(string)
if descFound {
result.Desc = desc.(string)
}
if indFound {
result.Index = ind.(int64)
}
if sinceFound {
result.Since = calculateTimeSince(since.(string))
}
result.ForSale = GetVendorProducts(db, rowid)
results = append(results, result)
}
}
return results, nil
}
func getPK(db *sqlite3.Conn, table string) int64 {
// find and return the most recently-inserted
// primary key, based on the table name
sql := fmt.Sprintf("select seq from sqlite_sequence where name='%s'", table)
var rowid int64
for s, err := db.Query(sql); err == nil; err = s.Next() {
s.Scan(&rowid)
}
return rowid
}
func getExistingItem(db *sqlite3.Conn, barcode, desc string) int64 {
// lookup the barcode and product desc
// combination and return the primary key,
// if the product has already been saved
args := sqlite3.NamedArgs{"$b": barcode, "$d": desc}
var rowid int64
rowid = BAD_PK // default value, in case no match
for s, err := db.Query(GET_EXISTING_ITEM, args); err == nil; err = s.Next() {
s.Scan(&rowid)
}
return rowid
}
func GetAllVendors(db *sqlite3.Conn) []*Vendor {
results := make([]*Vendor, 0)
row := make(sqlite3.RowMap)
for s, err := db.Query(GET_VENDORS); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
vendor, vendorFound := row["vendor_id"]
vendorName, vendorNameFound := row["display_name"]
if vendorFound && vendorNameFound {
v := Vendor{Id: rowid, VendorId: vendor.(string), DisplayName: vendorName.(string)}
results = append(results, &v)
}
}
return results
}
func GetVendor(db *sqlite3.Conn, vendorId int64) *Vendor {
result := new(Vendor)
row := make(sqlite3.RowMap)
args := sqlite3.NamedArgs{"$i": vendorId}
for s, err := db.Query(GET_VENDOR, args); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
vendor, vendorFound := row["vendor_id"]
vendorName, vendorNameFound := row["display_name"]
if vendorFound && vendorNameFound {
result.Id = rowid
result.VendorId = vendor.(string)
result.DisplayName = vendorName.(string)
}
}
return result
}
func GetAccount(db *sqlite3.Conn, email string) (*Account, error) {
// get the account corresponding to this email
result := new(Account)
args := sqlite3.NamedArgs{"$e": email}
row := make(sqlite3.RowMap)
for s, err := db.Query(GET_ACCOUNT, args); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
api, apiFound := row["api_code"]
if apiFound {
result.APICode = api.(string)
result.Id = rowid
result.Email = email
break
}
}
return result, nil
}
func GetAllAccounts(db *sqlite3.Conn) ([]*Account, error) {
// find all the accounts currently registered
results := make([]*Account, 0)
row := make(sqlite3.RowMap)
for s, err := db.Query(GET_ACCOUNTS); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
email, emailFound := row["email"]
api, apiFound := row["api_code"]
if emailFound && apiFound {
result := new(Account)
result.APICode = api.(string)
result.Id = rowid
result.Email = email.(string)
results = append(results, result)
}
}
return results, nil
}
func GetVendorProducts(db *sqlite3.Conn, itemId int64) []*VendorProduct {
results := make([]*VendorProduct, 0)
row := make(sqlite3.RowMap)
args := sqlite3.NamedArgs{"$i": itemId}
for s, err := db.Query(GET_VENDOR_PRODUCT, args); err == nil; err = s.Next() {
var rowid int64
s.Scan(&rowid, row)
vendorPk, vendorPkFound := row["id"]
productCode, productCodeFound := row["product_code"]
if vendorPkFound && productCodeFound {
result := new(VendorProduct)
result.Id = rowid
result.ProductCode = productCode.(string)
result.Vendor = GetVendor(db, vendorPk.(int64))
results = append(results, result)
}
}
return results
}
func create_table(tbname string, tbcreate string, conn *sqlite3.Conn) (created bool) {
tbcount := -1
created = false
query := "SELECT count(*) as count FROM sqlite_master " +
"WHERE type='table' AND name='" + tbname + "'"
ret, err := conn.Query(query)
if err != nil {
Err("create_table error: '" + tbname + "': " + err.Error())
} else {
ret.Scan(&tbcount)
ret.Close()
if tbcount <= 0 {
err := conn.Exec(tbcreate)
if err != nil {
panic("Can't create table: " + tbname + ", " + err.Error())
} else {
Debug(tbname + " created")
created = true
}
}
}
return created
}
