// Update the charac in the database
func (u *Charac) Update(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("UPDATE \"charac\" SET "+Charac_UpdateStr+" WHERE id=:id", u)
if err != nil {
err = errors.New("model.charac::Update " + err.Error())
}
return
}
// DeleteAuthors deletes the author linked to a database
func (d *Database) DeleteAuthors(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("DELETE FROM \"database__authors\" WHERE database_id=:id", d)
if err != nil {
err = errors.New("database::DeleteAuthors: " + err.Error())
}
return
}
// UpdateHandle links continents to a database
func (d *Database) UpdateHandle(tx *sqlx.Tx, handle *Database_handle) (err error) {
_, err = tx.NamedExec("UPDATE database_handle SET "+Database_handle_UpdateStr+" WHERE id = :id", handle)
if err != nil {
err = errors.New("database::UpdateHandle: " + err.Error())
}
return
}
// CacheDates get database sites extend and cache enveloppe
func (d *Database) CacheDates(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("UPDATE database SET start_date = (SELECT COALESCE(min(start_date1),-2147483648) FROM site_range WHERE site_id IN (SELECT id FROM site where database_id = :id) AND start_date1 != -2147483648), end_date = (SELECT COALESCE(max(end_date2),2147483647) FROM site_range WHERE site_id IN (SELECT id FROM site where database_id = :id) AND end_date2 != 2147483647) WHERE id = :id", d)
if err != nil {
err = errors.New("database::CheckDates: " + err.Error())
}
return
}
// DeleteContexts deletes the context linked to a database
func (d *Database) DeleteContexts(tx *sqlx.Tx) error {
_, err := tx.NamedExec("DELETE FROM \"database_context\" WHERE database_id=:id", d)
if err != nil {
err = errors.New("database::DeleteContexts: " + err.Error())
}
return err
}
// SetUsers of the group
func (g *Group) SetUsers(tx *sqlx.Tx, users []User) error {
ids := make([]string, len(users)+1)
for i, user := range users {
ids[i] = fmt.Sprintf("%d", user.Id)
}
ids[len(users)] = "-1" // empty list is a problem
_, err := tx.NamedExec("DELETE FROM \"user__group\" WHERE group_id=:id AND user_id NOT IN ("+strings.Join(ids, ",")+")", g)
if err != nil {
return err
}
rows, err := tx.Queryx("SELECT user_id FROM user__group WHERE group_id = $1 AND user_id IN ("+strings.Join(ids, ",")+")", g.Id)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var id string
rows.Scan(&id)
ids = removeString(ids, id)
}
ids = removeString(ids, "-1")
for _, userid := range ids {
_, err := tx.Exec("INSERT INTO user__group (group_id, user_id) VALUES ($1, $2)", g.Id, userid)
if err != nil {
return err
}
}
return nil
}
// SetCompanies set user companies
func (u *User) SetCompanies(tx *sqlx.Tx, companies []Company) error {
ids := make([]string, len(companies)+1)
for i, company := range companies {
ids[i] = fmt.Sprintf("%d", company.Id)
}
ids[len(companies)] = "-1" // empty list is a problem
_, err := tx.NamedExec("DELETE FROM \"user__company\" WHERE user_id=:id AND company_id NOT IN ("+strings.Join(ids, ",")+")", u)
if err != nil {
return err
}
rows, err := tx.Queryx("SELECT company_id FROM user__company WHERE user_id = $1 AND company_id IN ("+strings.Join(ids, ",")+")", u.Id)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var id string
rows.Scan(&id)
ids = removeString(ids, id)
}
ids = removeString(ids, "-1")
for _, companyid := range ids {
_, err := tx.Exec("INSERT INTO user__company (user_id, company_id) VALUES ($1, $2)", u.Id, companyid)
if err != nil {
return err
}
}
return nil
}
// SetGroups set user groups
func (u *User) SetGroups(tx *sqlx.Tx, groups []Group) error {
ids := make([]string, len(groups))
for i, group := range groups {
ids[i] = fmt.Sprintf("%d", group.Id)
}
_, err := tx.NamedExec("DELETE FROM \"user__group\" WHERE user_id=:id AND group_id NOT IN ("+strings.Join(ids, ",")+")", u)
if err != nil {
return err
}
rows, err := tx.Queryx("SELECT group_id FROM user__group WHERE user_id = $1 AND group_id IN ("+strings.Join(ids, ",")+")", u.Id)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var id string
rows.Scan(&id)
ids = removeString(ids, id)
}
for _, groupid := range ids {
_, err := tx.Exec("INSERT INTO user__group (user_id, group_id) VALUES ($1, $2)", u.Id, groupid)
if err != nil {
return err
}
}
return nil
}
// Delete the charac from the database
func (u *Charac) Delete(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("DELETE FROM \"charac\" WHERE id=:id", u)
if err != nil {
err = errors.New("model.charac::Delete " + err.Error())
}
return
}
// Update database informations
func (d *Database) Update(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("UPDATE \"database\" SET "+Database_UpdateStr+" WHERE id=:id", d)
if err != nil {
err = errors.New("database::Update: " + err.Error())
}
return
}
// Update the charac_root in the database
func (u *Charac_root) Update(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("UPDATE \"charac_root\" SET \"admin_group_id\" = :admin_group_id, cached_langs = :cached_langs WHERE root_charac_id=:root_charac_id", u)
if err != nil {
err = errors.New("model.charac_root::Update" + err.Error())
}
return
}
// Update the shapefile in the shapefile
func (u *Shapefile) Update(tx *sqlx.Tx) error {
fmt.Println("UPDATE \"shapefile\" SET " + Shapefile_UpdateStr + " WHERE id=:id")
//q := "UPDATE \"shapefile\" SET \"creator_user_id\" = :creator_user_id, \"filename\" = :filename, \"md5sum\" = :md5sum, \"geojson_with_data\" = :geojson_with_data, \"geojson\" = :geojson, \"start_date\" = :start_date, \"end_date\" = :end_date, \"geographical_extent_geom\" = ST_GeomFromGeoJSON(:geographical_extent_geom), \"published\" = :published, \"license\" = :license, \"license_id\" = :license_id, \"declared_creation_date\" = :declared_creation_date, \"updated_at\" = now() WHERE id=:id"
// Do not update geojson datas
q := "UPDATE \"shapefile\" SET \"creator_user_id\" = :creator_user_id, \"filename\" = :filename, \"md5sum\" = :md5sum, \"start_date\" = :start_date, \"end_date\" = :end_date, \"geographical_extent_geom\" = ST_GeomFromGeoJSON(:geographical_extent_geom), \"published\" = :published, \"license\" = :license, \"license_id\" = :license_id, \"declared_creation_date\" = :declared_creation_date, \"updated_at\" = now() WHERE id=:id"
_, err := tx.NamedExec(q, u)
return err
}
// CacheGeom get database sites extend and cache enveloppe
func (d *Database) CacheGeom(tx *sqlx.Tx) (err error) {
// Extent
//_, err = tx.NamedExec("SELECT ST_Envelope(sites.geom::::geometry) FROM (SELECT geom FROM site WHERE database_id = :id) as sites", d)
// Envelope
_, err = tx.NamedExec("UPDATE database SET geographical_extent_geom = (SELECT (ST_Envelope((SELECT ST_Multi(ST_Collect(f.geom)) as singlegeom FROM (SELECT (ST_Dump(geom::::geometry)).geom As geom FROM site WHERE database_id = :id) As f)))) WHERE id = :id", d)
if err != nil {
err = errors.New("database::CacheGeom: " + err.Error())
}
return
}
// increment referer balance
func incrementRefererBalance(tx *sqlx.Tx, refererID int64, delta float64) (int64, error) {
result, err := tx.NamedExec("UPDATE users SET `balance` = `balance` + :delta, `total_income_from_referees` = `total_income_from_referees` + :delta WHERE id = :id", map[string]interface{}{
"id": refererID,
"delta": delta,
})
if err != nil {
return 0, fmt.Errorf("increment referer balance error: %v", err)
}
rowAffected, _ := result.RowsAffected()
return rowAffected, nil
}
// increment total reward
func incrementTotalReward(tx *sqlx.Tx, totalReward float64, now time.Time) error {
sql := "INSERT INTO total_rewards (`total`, `created_at`) VALUES (:delta, :created_at) ON DUPLICATE KEY UPDATE `total` = `total` + :delta"
args := map[string]interface{}{
"delta": totalReward,
"created_at": now,
}
if _, err := tx.NamedExec(sql, args); err != nil {
return fmt.Errorf("increment total reward error: %v", err)
}
return nil
}
func createPtcwallIncomeWithTx(tx *sqlx.Tx, income models.Income) error {
incomeID, err := addIncome(tx, income)
if err != nil {
return err
}
// insert ptcwall offer
offer := models.PtcwallOffer{
IncomeID: incomeID,
UserID: income.UserID,
Amount: income.Income,
}
_, err = tx.NamedExec("INSERT INTO `ptcwalls` (`income_id`, `user_id`, `amount`) VALUE (:income_id, :user_id, :amount)", offer)
return err
}
func createPersonalyIncomeWithTx(tx *sqlx.Tx, income models.Income, offerID string) error {
incomeID, err := addIncome(tx, income)
if err != nil {
return err
}
// insert personaly offer
offer := models.PersonalyOffer{
IncomeID: incomeID,
UserID: income.UserID,
OfferID: offerID,
Amount: income.Income,
}
_, err = tx.NamedExec("INSERT INTO `personaly` (`income_id`, `user_id`, `offer_id`, `amount`) VALUE (:income_id, :user_id, :offer_id, :amount)", offer)
return err
}
func createOffertoroIncomeWithTx(tx *sqlx.Tx, income models.Income, transactionID, offerID string) error {
incomeID, err := addIncome(tx, income)
if err != nil {
return err
}
// insert offertoro offer
offer := models.Offertoro{
IncomeID: incomeID,
UserID: income.UserID,
TransactionID: transactionID,
OfferID: offerID,
Amount: income.Income,
}
_, err = tx.NamedExec("INSERT INTO `offertoro` (`income_id`, `user_id`, `transaction_id`, `offer_id`, `amount`) VALUE (:income_id, :user_id, :transaction_id, :offer_id, :amount)", offer)
return err
}
func createAdscendMediaIncomeWithTx(tx *sqlx.Tx, income models.Income, transactionID, offerID string) error {
// insert income into incomes table
incomeID, err := addIncome(tx, income)
if err != nil {
return err
}
// insert adscend media offer
offer := models.AdscendMedia{
IncomeID: incomeID,
UserID: income.UserID,
TransactionID: transactionID,
OfferID: offerID,
Amount: income.Income,
}
_, err = tx.NamedExec("INSERT INTO `adscend_media` (`income_id`, `user_id`, `transaction_id`, `offer_id`, `amount`) VALUE (:income_id, :user_id, :transaction_id, :offer_id, :amount)", offer)
return err
}
// UpdateFields updates "database" fields (crazy isn't it ?)
func (d *Database) UpdateFields(tx *sqlx.Tx, params interface{}, fields ...string) (err error) {
var upd string
for i, f := range fields {
upd += "\"" + f + "\" = :" + f
if i+1 < len(fields) {
upd += ", "
}
}
query := "UPDATE \"database\" SET " + upd + " WHERE id = :id"
if err != nil {
err = errors.New("database::UpdateFields: " + err.Error())
}
_, err = tx.NamedExec(query, params)
return
}
// insert reward income into incomes table
func addIncome(tx *sqlx.Tx, income models.Income) (int64, error) {
sql := "INSERT INTO incomes (`user_id`, `referer_id`, `type`, `income`, `referer_income`) VALUES (:user_id, :referer_id, :type, :income, :referer_income)"
// pending offerwall income
if income.Type != models.IncomeTypeReward {
income.Status = models.IncomeStatusPending
sql = "INSERT INTO incomes (`user_id`, `referer_id`, `type`, `income`, `referer_income`, `status`) VALUES (:user_id, :referer_id, :type, :income, :referer_income, :status)"
}
result, err := tx.NamedExec(sql, income)
if err != nil {
return 0, fmt.Errorf("add income error: %v", err)
}
lastInsertID, err := result.LastInsertId()
if err != nil {
return 0, err
}
return lastInsertID, nil
}
// Delete database and all infos associated
func (d *Database) Delete(tx *sqlx.Tx) (err error) {
err = d.DeleteContexts(tx)
if err != nil {
return
}
err = d.DeleteContinents(tx)
if err != nil {
return
}
err = d.DeleteCountries(tx)
if err != nil {
return
}
err = d.DeleteAuthors(tx)
if err != nil {
return
}
err = d.DeleteHandles(tx)
if err != nil {
return
}
err = d.DeleteImports(tx)
if err != nil {
return
}
err = d.DeleteSites(tx)
if err != nil {
return
}
_, err = tx.NamedExec("DELETE FROM \"database_tr\" WHERE database_id=:id", d)
if err != nil {
return
}
_, err = tx.NamedExec("DELETE FROM \"project__database\" WHERE database_id=:id", d)
if err != nil {
return
}
_, err = tx.NamedExec("DELETE FROM \"database\" WHERE id=:id", d)
return
}
// Delete the map layer from the database
func (u *Map_layer) Delete(tx *sqlx.Tx) error {
_, err := tx.NamedExec("DELETE FROM \"map_layer\" WHERE id=:id", u)
return err
}
// DeleteAuthors deletes the author linked to a wms layer
func (u *Map_layer) DeleteAuthors(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("DELETE FROM \"map_layer__authors\" WHERE map_layer_id=:id", u)
return
}
// Delete the shapefile from the database
func (u *Shapefile) Delete(tx *sqlx.Tx) error {
_, err := tx.NamedExec("DELETE FROM \"shapefile\" WHERE id=:id", u)
return err
}
// Set publication state of the shapefile
func (u *Shapefile) SetPublicationState(tx *sqlx.Tx) error {
_, err := tx.NamedExec("UPDATE \"shapefile\" SET published = :published WHERE id=:id", u)
return err
}
// DeleteAuthors deletes the author linked to a shapefile
func (u *Shapefile) DeleteAuthors(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("DELETE FROM \"shapefile__authors\" WHERE shapefile_id=:id", u)
return
}
// Update the company in the database
func (u *Company) Update(tx *sqlx.Tx) error {
_, err := tx.NamedExec("UPDATE \"company\" SET "+Company_UpdateStr+" WHERE id=:id", u)
return err
}
// DeleteSites deletes all sites linked to a database
func (d *Database) DeleteSites(tx *sqlx.Tx) (err error) {
_, err = tx.NamedExec("DELETE FROM \"site\" WHERE database_id=:id", d)
return
}
// Delete the company from the database
func (u *Company) Delete(tx *sqlx.Tx) error {
_, err := tx.NamedExec("DELETE FROM \"company\" WHERE id=:id", u)
return err
}
