471 lines
12 KiB
Go
471 lines
12 KiB
Go
|
package models
|
||
|
|
|
||
|
|
import (
|
||
|
|
"bytes"
|
||
|
|
"database/sql"
|
||
|
|
"errors"
|
||
|
|
"strconv"
|
||
|
|
"strings"
|
||
|
|
|
||
|
|
"brainminder.speedtech.it/internal/database"
|
||
|
|
)
|
||
|
|
|
||
|
|
type Item struct {
|
||
|
|
Id int64 `db:"id"`
|
||
|
|
Title string `db:"title"`
|
||
|
|
Summary string `db:"summary"`
|
||
|
|
Summary_rendered string `db:"summary_rendered"`
|
||
|
|
Description string `db:"description"`
|
||
|
|
Description_rendered string `db:"description_rendered"`
|
||
|
|
Active int `db:"active"`
|
||
|
|
Tags string `db:"tags"`
|
||
|
|
Type_id int64 `db:"type_id"`
|
||
|
|
Categories string `db:"categories"`
|
||
|
|
Notebooks string `db:"notebooks"`
|
||
|
|
Crypted int `db:"crypted"`
|
||
|
|
Hidden int `db:"hidden"`
|
||
|
|
On_dashboard int `db:"on_dashboard"`
|
||
|
|
On_dashboard_position int `db:"on_dashboard_position"`
|
||
|
|
Type_title string `db:"type_title"`
|
||
|
|
Type_icon string `db:"type_icon"`
|
||
|
|
Type_show_summary int `db:"type_show_summary"`
|
||
|
|
Type_show_description int `db:"type_show_description"`
|
||
|
|
FieldsOnList []Field
|
||
|
|
FieldsValues []FieldValue
|
||
|
|
FieldsValuesMap map[int64]map[int]string
|
||
|
|
}
|
||
|
|
|
||
|
|
type ItemRelation struct {
|
||
|
|
Item_id int64 `db:"item_id"`
|
||
|
|
Related_item_id int64 `db:"related_item_id"`
|
||
|
|
Relation_type string `db:"relation_type"`
|
||
|
|
Title string `db:"title"`
|
||
|
|
Categories string `db:"categories"`
|
||
|
|
Tags string `db:"tags"`
|
||
|
|
Type_title string `db:"type_title"`
|
||
|
|
Type_icon string `db:"type_icon"`
|
||
|
|
}
|
||
|
|
|
||
|
|
type ItemModel struct {
|
||
|
|
*BaseModel
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) One(id int64) (*Item, bool, error) {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
var row Item
|
||
|
|
|
||
|
|
query := `SELECT bmi.*, bmt.title AS type_title, bmt.icon AS type_icon, bmt.show_summary AS type_show_summary,
|
||
|
|
bmt.show_description AS type_show_description FROM bm_item bmi
|
||
|
|
INNER JOIN bm_type bmt ON bmi.type_id=bmt.id WHERE bmi.id = $1`
|
||
|
|
|
||
|
|
err := model.DB.GetContext(ctx, &row, query, id)
|
||
|
|
if errors.Is(err, sql.ErrNoRows) {
|
||
|
|
return nil, false, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
return &row, true, err
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) Delete(id int) (bool, error) {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
_, err := model.DB.ExecContext(ctx, `DELETE FROM bm_item WHERE id = $1`, id)
|
||
|
|
if errors.Is(err, sql.ErrNoRows) {
|
||
|
|
return false, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
_, err = model.DB.ExecContext(ctx, `DELETE FROM bm_item_relations WHERE id = $1 OR related_item_id=$1`, id)
|
||
|
|
if errors.Is(err, sql.ErrNoRows) {
|
||
|
|
return false, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
_, err = model.DB.ExecContext(ctx, `DELETE FROM bm_item_fields WHERE item_id = $1`, id)
|
||
|
|
if errors.Is(err, sql.ErrNoRows) {
|
||
|
|
return false, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
_, err = model.DB.ExecContext(ctx, `DELETE FROM bm_item_keywords WHERE item_id = $1`, id)
|
||
|
|
if errors.Is(err, sql.ErrNoRows) {
|
||
|
|
return false, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
return true, err
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) Search(searchText string, criteria map[string]any) ([]Item, bool, error) {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
var params []interface{}
|
||
|
|
var conditions []string
|
||
|
|
var cond string
|
||
|
|
|
||
|
|
var conditions_criteria []string
|
||
|
|
var cond_criteria string
|
||
|
|
|
||
|
|
query := `SELECT DISTINCT bmi.*, bmt.title AS type_title, bmt.icon AS type_icon FROM bm_item bmi
|
||
|
|
INNER JOIN bm_type bmt ON bmi.type_id=bmt.id
|
||
|
|
INNER JOIN bm_item_keywords bit ON bit.item_id=bmi.id `
|
||
|
|
|
||
|
|
for field, value := range criteria {
|
||
|
|
switch field {
|
||
|
|
case "notebook_id":
|
||
|
|
if value != nil {
|
||
|
|
valint := value.(int64)
|
||
|
|
if valint > 0 {
|
||
|
|
valstr := "|" + strconv.FormatInt(valint, 10) + "|"
|
||
|
|
params = append(params, valstr)
|
||
|
|
conditions_criteria = append(conditions_criteria, "INSTR(bmi.notebooks, ?) > 0 ")
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
if len(searchText) > 0 {
|
||
|
|
params = append(params, strings.ToLower(searchText))
|
||
|
|
conditions = append(conditions, "bit.keyword = ? ")
|
||
|
|
}
|
||
|
|
|
||
|
|
for _, s := range strings.Split(searchText, " ") {
|
||
|
|
s = strings.ToLower(s)
|
||
|
|
if len(s) > 0 {
|
||
|
|
params = append(params, s)
|
||
|
|
conditions = append(conditions, "bit.keyword = ? ")
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
for _, condition := range conditions {
|
||
|
|
if len(cond) > 0 {
|
||
|
|
cond = cond + " OR "
|
||
|
|
}
|
||
|
|
cond = cond + condition
|
||
|
|
}
|
||
|
|
|
||
|
|
for _, condition := range conditions_criteria {
|
||
|
|
if len(cond_criteria) > 0 {
|
||
|
|
cond_criteria = cond_criteria + " AND "
|
||
|
|
}
|
||
|
|
cond_criteria = cond_criteria + condition
|
||
|
|
}
|
||
|
|
|
||
|
|
len_cond := len(cond)
|
||
|
|
len_cond_criteria := len(cond_criteria)
|
||
|
|
|
||
|
|
if len_cond_criteria > 0 || len_cond > 0 {
|
||
|
|
query = query + "WHERE "
|
||
|
|
}
|
||
|
|
|
||
|
|
if len_cond_criteria > 0 {
|
||
|
|
query = query + cond_criteria
|
||
|
|
if len_cond > 0 {
|
||
|
|
query = query + " AND (" + cond + ")"
|
||
|
|
}
|
||
|
|
} else {
|
||
|
|
if len_cond > 0 {
|
||
|
|
query = query + " (" + cond + ")"
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
query = query + ` ORDER BY bmi.title`
|
||
|
|
|
||
|
|
var rows []Item
|
||
|
|
|
||
|
|
err := model.DB.SelectContext(ctx, &rows, query, params...)
|
||
|
|
if errors.Is(err, sql.ErrNoRows) {
|
||
|
|
return nil, false, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
return rows, true, err
|
||
|
|
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) Find(criteria map[string]any, offset int64) ([]Item, bool, error) {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
var params []interface{}
|
||
|
|
var conditions []string
|
||
|
|
var cond string
|
||
|
|
query := `SELECT DISTINCT bmi.*, bmt.title AS type_title, bmt.icon AS type_icon FROM bm_item bmi
|
||
|
|
INNER JOIN bm_type bmt ON bmi.type_id=bmt.id `
|
||
|
|
|
||
|
|
for field, value := range criteria {
|
||
|
|
switch field {
|
||
|
|
case "Title":
|
||
|
|
valstr := value.(string)
|
||
|
|
if len(valstr) > 0 {
|
||
|
|
params = append(params, valstr)
|
||
|
|
conditions = append(conditions, "bmi.title LIKE '%' || ? || '%'")
|
||
|
|
}
|
||
|
|
case "Tags":
|
||
|
|
valstr := value.(string)
|
||
|
|
if len(valstr) > 0 {
|
||
|
|
params = append(params, valstr)
|
||
|
|
conditions = append(conditions, "bmi.tags LIKE '%' || ? || '%'")
|
||
|
|
}
|
||
|
|
case "type_id":
|
||
|
|
valint := value.(int64)
|
||
|
|
if valint > 0 {
|
||
|
|
params = append(params, valint)
|
||
|
|
conditions = append(conditions, "bmi.type_id=?")
|
||
|
|
}
|
||
|
|
case "notebook_id":
|
||
|
|
if value != nil {
|
||
|
|
valint := value.(int64)
|
||
|
|
if valint > 0 {
|
||
|
|
valstr := "|" + strconv.FormatInt(valint, 10) + "|"
|
||
|
|
params = append(params, valstr)
|
||
|
|
conditions = append(conditions, "INSTR(bmi.notebooks, ?) > 0")
|
||
|
|
}
|
||
|
|
}
|
||
|
|
case "category_id":
|
||
|
|
if value != nil {
|
||
|
|
valint := value.(int64)
|
||
|
|
if valint > 0 {
|
||
|
|
valstr := "|" + strconv.FormatInt(valint, 10) + "|"
|
||
|
|
params = append(params, valstr)
|
||
|
|
conditions = append(conditions, "INSTR(bmi.categories, ?) > 0")
|
||
|
|
}
|
||
|
|
}
|
||
|
|
case "On_dashboard":
|
||
|
|
valint := value.(int)
|
||
|
|
if valint > 0 {
|
||
|
|
params = append(params, valint)
|
||
|
|
conditions = append(conditions, "bmi.on_dashboard=?")
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
for _, condition := range conditions {
|
||
|
|
if len(cond) > 0 {
|
||
|
|
cond = cond + " AND "
|
||
|
|
}
|
||
|
|
cond = cond + condition
|
||
|
|
}
|
||
|
|
|
||
|
|
if len(cond) > 0 {
|
||
|
|
query = query + "WHERE " + cond + " "
|
||
|
|
}
|
||
|
|
|
||
|
|
query = query + ` ORDER BY bmi.title`
|
||
|
|
|
||
|
|
params = append(params, offset)
|
||
|
|
query = query + ` LIMIT 10 OFFSET ?`
|
||
|
|
|
||
|
|
var rows []Item
|
||
|
|
|
||
|
|
err := model.DB.SelectContext(ctx, &rows, query, params...)
|
||
|
|
if errors.Is(err, sql.ErrNoRows) {
|
||
|
|
return nil, false, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
return rows, true, err
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) Create(Item *Item) (int64, error) {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
markdown := *model.GetMarkdown()
|
||
|
|
|
||
|
|
var bufSummary bytes.Buffer
|
||
|
|
markdown.Convert([]byte(Item.Summary), &bufSummary)
|
||
|
|
Item.Summary_rendered = bufSummary.String()
|
||
|
|
|
||
|
|
var bufDescription bytes.Buffer
|
||
|
|
markdown.Convert([]byte(Item.Description), &bufDescription)
|
||
|
|
Item.Description_rendered = bufDescription.String()
|
||
|
|
|
||
|
|
query := `INSERT INTO bm_item (type_id, title, summary, summary_rendered, description, description_rendered, on_dashboard, tags, notebooks, categories)
|
||
|
|
VALUES (:type_id, :title, :summary, :summary_rendered, :description, :description_rendered, :on_dashboard, :tags, :notebooks, :categories)`
|
||
|
|
|
||
|
|
result, err := model.DB.NamedExecContext(ctx, query, Item)
|
||
|
|
if err != nil {
|
||
|
|
return 0, err
|
||
|
|
}
|
||
|
|
|
||
|
|
id, err := result.LastInsertId()
|
||
|
|
if err != nil {
|
||
|
|
return 0, err
|
||
|
|
}
|
||
|
|
|
||
|
|
return id, err
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) Update(Item *Item) error {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
markdown := *model.GetMarkdown()
|
||
|
|
|
||
|
|
var bufSummary bytes.Buffer
|
||
|
|
markdown.Convert([]byte(Item.Summary), &bufSummary)
|
||
|
|
Item.Summary_rendered = bufSummary.String()
|
||
|
|
|
||
|
|
var bufDescription bytes.Buffer
|
||
|
|
markdown.Convert([]byte(Item.Description), &bufDescription)
|
||
|
|
Item.Description_rendered = bufDescription.String()
|
||
|
|
|
||
|
|
query := `UPDATE bm_item SET title=:title, type_id=:type_id, summary=:summary, summary_rendered=:summary_rendered,
|
||
|
|
description=:description, description_rendered=:description_rendered, tags=:tags, on_dashboard=:on_dashboard,
|
||
|
|
categories=:categories, notebooks =:notebooks WHERE id = :id`
|
||
|
|
_, err := model.DB.NamedExecContext(ctx, query, Item)
|
||
|
|
|
||
|
|
if err != nil {
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) AddToDashboard(id int64) error {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
query := `UPDATE bm_item SET on_dashboard=1 WHERE id = :id`
|
||
|
|
_, err := model.DB.ExecContext(ctx, query, id)
|
||
|
|
|
||
|
|
if err != nil {
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) RemoveFromDashboard(id int64) error {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
query := `UPDATE bm_item SET on_dashboard=0 WHERE id = :id`
|
||
|
|
_, err := model.DB.ExecContext(ctx, query, id)
|
||
|
|
|
||
|
|
if err != nil {
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) SaveKeywords(Item *Item, fields *[]Field, fieldsValues map[int64]map[int]string) error {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
var keywords []string
|
||
|
|
keywords = append(keywords, Item.Type_title)
|
||
|
|
|
||
|
|
var categories_int []int64
|
||
|
|
categories_str := strings.Split(strings.Trim(Item.Categories, "|"), "|")
|
||
|
|
for _, category_str := range categories_str {
|
||
|
|
category_int, _ := strconv.ParseInt(category_str, 10, 64)
|
||
|
|
categories_int = append(categories_int, category_int)
|
||
|
|
}
|
||
|
|
|
||
|
|
categoryModel := &CategoryModel{DB: model.DB}
|
||
|
|
categories, _, _ := categoryModel.Find(categories_int)
|
||
|
|
for _, category := range categories {
|
||
|
|
keywords = append(keywords, category.Name)
|
||
|
|
}
|
||
|
|
|
||
|
|
keywords = append(keywords, strings.Split(Item.Tags, ",")...)
|
||
|
|
keywords = append(keywords, strings.Split(Item.Title, " ")...)
|
||
|
|
|
||
|
|
for _, field := range *fields {
|
||
|
|
if field.Widget != "url" {
|
||
|
|
values, found := fieldsValues[field.Type_field_id]
|
||
|
|
if found {
|
||
|
|
for _, value := range values {
|
||
|
|
keywords = append(keywords, value)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
query := `DELETE FROM bm_item_keywords WHERE item_id = $1`
|
||
|
|
_, err := model.DB.ExecContext(ctx, query, Item.Id)
|
||
|
|
|
||
|
|
if err != nil {
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
|
||
|
|
stmt, err := model.DB.Prepare(`INSERT INTO bm_item_keywords (item_id, keyword) VALUES($1, $2)`)
|
||
|
|
for _, keyword := range keywords {
|
||
|
|
keyword = strings.ToLower(strings.TrimSpace(keyword))
|
||
|
|
if len(keyword) > 1 {
|
||
|
|
_, err = stmt.ExecContext(ctx, Item.Id, keyword)
|
||
|
|
}
|
||
|
|
|
||
|
|
if err != nil {
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
return nil
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) AddRelation(id int64, related_id int64, relation_type string) error {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
query := `INSERT INTO bm_item_relations (item_id, related_item_id, relation_type) VALUES($1, $2, $3)`
|
||
|
|
_, err := model.DB.ExecContext(ctx, query, id, related_id, relation_type)
|
||
|
|
|
||
|
|
if err != nil {
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
return nil
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) UpdateRelation(id int64, related_id int64, relation_type string) error {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
query := `UPDATE bm_item_relations SET relation_type=$1 WHERE item_id=$2 AND related_item_id=$3`
|
||
|
|
_, err := model.DB.ExecContext(ctx, query, relation_type, id, related_id)
|
||
|
|
|
||
|
|
if err != nil {
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
return nil
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) DeleteRelation(id int64, related_id int64) error {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
query := `DELETE FROM bm_item_relations WHERE item_id=$1 AND related_item_id=$2`
|
||
|
|
_, err := model.DB.ExecContext(ctx, query, id, related_id)
|
||
|
|
|
||
|
|
if err != nil {
|
||
|
|
return err
|
||
|
|
}
|
||
|
|
return nil
|
||
|
|
}
|
||
|
|
|
||
|
|
func (model *ItemModel) GetRelations(id int64) ([]ItemRelation, bool, error) {
|
||
|
|
ctx, cancel := database.GetContext()
|
||
|
|
defer cancel()
|
||
|
|
|
||
|
|
query := `SELECT bir.item_id, bir.related_item_id, bmi.title, bmi.categories, bmi.tags, bmt.title AS type_title,
|
||
|
|
bmt.icon AS type_icon, bir.relation_type FROM bm_item bmi
|
||
|
|
INNER JOIN bm_type bmt ON bmi.type_id=bmt.id
|
||
|
|
INNER JOIN bm_item_relations bir ON bir.related_item_id=bmi.id WHERE bir.item_id=$1
|
||
|
|
UNION
|
||
|
|
SELECT bir.item_id, bir.related_item_id, bmi.title, bmi.categories, bmi.tags, bmt.title AS type_title,
|
||
|
|
bmt.icon AS type_icon, bir.relation_type FROM bm_item bmi
|
||
|
|
INNER JOIN bm_type bmt ON bmi.type_id=bmt.id
|
||
|
|
INNER JOIN bm_item_relations bir ON bir.item_id=bmi.id WHERE bir.related_item_id=$1
|
||
|
|
`
|
||
|
|
|
||
|
|
var rows []ItemRelation
|
||
|
|
|
||
|
|
err := model.DB.SelectContext(ctx, &rows, query, id)
|
||
|
|
if errors.Is(err, sql.ErrNoRows) {
|
||
|
|
return nil, false, nil
|
||
|
|
}
|
||
|
|
|
||
|
|
return rows, true, err
|
||
|
|
}
|