vkko/back/material.go

package back

import (
	"database/sql"
	"errors"
	"html/template"
)

type limit struct {
	Limit  int `json:"Limit"`
	Offset int `json:"Offset"`
}

type search struct {
	Phrase string `json:"Phrase"`
}

type material struct {
	Link         string         `json:"Link"`
	Caption      string         `json:"Caption"`
	Content      template.HTML  `json:"Content"`
	Source       string         `json:"Source"`
	SourceImages string         `json:"SourceImages"`
	Preview      sql.NullString `json:"Preview"`
	Image        sql.NullString `json:"Image"`
	Published    string         `json:"Published"`
	Active       bool           `json:"Active"`
	Category     string         `json:"Category"`
	Author       int32          `json:"Author"`

	Rows []material
}

func prepareQueriesMaterial() []string {
	var e error
	var ebox []string

	queries["Insert#Material#"], e = db.Prepare(`INSERT INTO "Material"(
		"Link", "Caption", "Content", "Source", "SourceImages", "Preview", "Image", "Published", "Active", "Category", "Author")
		VALUES ($1, $2, $3, $4, $5, $6, $7, CURRENT_TIMESTAMP, TRUE, $8, $9)`)
	if e != nil {
		ebox = append(ebox, "Insert#Material#"+e.Error())
	}

	queries["Update#Material#"], e = db.Prepare(`UPDATE "Material" SET "Caption"=$1, "Content"=$2, "Source"=$3, "Preview"=$4 WHERE "Link"=$5`)
	if e != nil {
		ebox = append(ebox, "Update#Material#"+e.Error())
	}
	queries["Delete#Material#"], e = db.Prepare(`UPDATE "Material" SET "Active"=$2 WHERE "Link"=$1`)
	if e != nil {
		ebox = append(ebox, "Delete#Material#"+e.Error())
	}
	queries["Select#Material#"], e = db.Prepare(`SELECT "Link", "Caption", "Category", "Active" FROM "Material" ORDER BY "Published" DESC`)
	if e != nil {
		ebox = append(ebox, "Select#Material# - "+e.Error())
	}
	queries["Select#Material#Limit"], e = db.Prepare(`SELECT "Link", "Caption", "Preview", "Image", "Published" FROM "Material" WHERE "Active"=TRUE AND "Category"=$3 ORDER BY "Published" DESC OFFSET $1 LIMIT $2`)
	if e != nil {
		ebox = append(ebox, "Select#Material#Limit - "+e.Error())
	}
	queries["Select#Material#LimitWithContent"], e = db.Prepare(`SELECT "Link", "Caption", "Preview", "Content", "Image", "Published" FROM "Material" WHERE "Active"=TRUE AND "Category"=$3 ORDER BY "Published" DESC OFFSET $1 LIMIT $2`)
	if e != nil {
		ebox = append(ebox, "Select#Material#LimitWithContent - "+e.Error())
	}
	queries["Select#Material#One"], e = db.Prepare(`SELECT "Caption", "Content", "Published", "Category", "Active" FROM "Material" WHERE "Link"=$1`)
	if e != nil {
		ebox = append(ebox, "Select#Material#One - "+e.Error())
	}
	queries["Select#Material#Count"], e = db.Prepare(`SELECT count(*) FROM "Material" WHERE "Active"=TRUE AND "Category"=$1`)
	if e != nil {
		ebox = append(ebox, "Select#Material#Count - "+e.Error())
	}
	queries["Select#Material#CountAll"], e = db.Prepare(`SELECT count(*) FROM "Material" WHERE "Category"=$1`)
	if e != nil {
		ebox = append(ebox, "Select#Material#CountAll - "+e.Error())
	}
	queries["Select#Material#Search"], e = db.Prepare(`SELECT "Link", "Caption", "Published", "Category" FROM "Material" WHERE ("Caption" ILIKE $1 OR "Content" ILIKE $1) AND "Active"=TRUE`)
	if e != nil {
		ebox = append(ebox, "Select#Material#Search - "+e.Error())
	}
	queries["Select#Material#Source"], e = db.Prepare(`SELECT "Source", "SourceImages", "Category", "Caption" FROM "Material" WHERE "Link"=$1`)
	if e != nil {
		ebox = append(ebox, "Select#Material#Source - "+e.Error())
	}

	return ebox
}

func (m *material) Insert() error {
	stmt, ok := queries["Insert#Material#"]
	if !ok {
		return errors.New("Запрос Insert#Material# не найден")
	}

	logger.Println(m.SourceImages)

	_, e := stmt.Exec(m.Link, m.Caption, m.Content, m.Source, m.SourceImages, m.Preview, m.Image, m.Category, m.Author)
	if e != nil {
		return e
	}

	return nil
}

func (m *material) Update() error {
	stmt, ok := queries["Update#Material#"]
	if !ok {
		return errors.New("Запрос Update#Material# не найден")
	}

	_, e := stmt.Exec(m.Caption, m.Content, m.Source, m.Preview, m.Link)
	if e != nil {
		return e
	}

	return nil
}

func (m *material) Delete() error {
	stmt, ok := queries["Delete#Material#"]
	if !ok {
		return errors.New("Запрос Delete#Material# не найден")
	}

	_, e := stmt.Exec(m.Link, m.Active)
	if e != nil {
		return e
	}

	return nil
}

func (m *material) Select(offset, limit int) error {

	stmt, ok := queries["Select#Material#Limit"]
	if !ok {
		return errors.New("Запрос Select#Material#Limit не найден")
	}

	rows, e := stmt.Query(offset, limit, m.Category)
	if e != nil {
		return e
	}
	defer rows.Close()

	for rows.Next() {

		// "Link", "Caption", "Content", "Image", "Published"
		e = rows.Scan(
			&m.Link,
			&m.Caption,
			&m.Preview,
			&m.Image,
			&m.Published,
		)
		if e != nil {
			return e
		}

		m.Rows = append(m.Rows, material{
			Link:      m.Link,
			Caption:   m.Caption,
			Preview:   m.Preview,
			Image:     m.Image,
			Published: m.Published,
		})
	}

	return nil
}

func (m *material) SelectWithContent(offset, limit int) error {

	stmt, ok := queries["Select#Material#LimitWithContent"]
	if !ok {
		return errors.New("Запрос Select#Material#LimitWithContent не найден")
	}

	rows, e := stmt.Query(offset, limit, m.Category)
	if e != nil {
		return e
	}
	defer rows.Close()

	for rows.Next() {

		// "Link", "Caption", "Content", "Image", "Published"
		e = rows.Scan(
			&m.Link,
			&m.Caption,
			&m.Preview,
			&m.Content,
			&m.Image,
			&m.Published,
		)
		if e != nil {
			return e
		}

		m.Rows = append(m.Rows, material{
			Link:      m.Link,
			Caption:   m.Caption,
			Preview:   m.Preview,
			Content:   m.Content,
			Image:     m.Image,
			Published: m.Published,
		})
	}

	return nil
}

func (m *material) SelectAll() error {

	stmt, ok := queries["Select#Material#"]
	if !ok {
		return errors.New("Запрос Select#Material# не найден")
	}

	rows, e := stmt.Query()
	if e != nil {
		return e
	}
	defer rows.Close()

	for rows.Next() {
		e = rows.Scan(
			&m.Link,
			&m.Caption,
			&m.Category,
			&m.Active,
		)
		if e != nil {
			return e
		}

		m.Rows = append(m.Rows, material{
			Link:     m.Link,
			Caption:  m.Caption,
			Category: m.Category,
			Active:   m.Active,
		})
	}

	return nil
}

func (m *material) SelectOne() error {

	stmt, ok := queries["Select#Material#One"]
	if !ok {
		return errors.New("Запрос Select#Material#One не найден")
	}

	row := stmt.QueryRow(m.Link)

	// "Link", "Caption", "Content", "Image", "Published"
	e := row.Scan(
		&m.Caption,
		&m.Content,
		&m.Published,
		&m.Category,
		&m.Active,
	)
	if e != nil {
		return e
	}

	m.Published = "Опубликовано " + getDate(m.Published)

	return nil
}

func (m *material) SelectCount() (int, error) {

	stmt, ok := queries["Select#Material#Count"]
	if !ok {
		return 0, errors.New("Запрос Select#Material#Count не найден")
	}

	row := stmt.QueryRow(m.Category)

	var count int
	e := row.Scan(&count)
	if e != nil {
		return 0, e
	}

	return count, nil
}

func (m *material) SelectCountAll() (int, error) {

	stmt, ok := queries["Select#Material#CountAll"]
	if !ok {
		return 0, errors.New("Запрос Select#Material#CountAll не найден")
	}

	row := stmt.QueryRow(m.Category)

	var count int
	e := row.Scan(&count)
	if e != nil {
		return 0, e
	}

	return count, nil
}

func (m *material) Search(phrase string) error {

	stmt, ok := queries["Select#Material#Search"]
	if !ok {
		return errors.New("Запрос Select#Material#Search не найден")
	}

	ilike := "%" + phrase + "%"

	rows, e := stmt.Query(ilike)
	if e != nil {
		logger.Println("ERROR", e)
		return e
	}
	defer rows.Close()

	for rows.Next() {

		// "Link", "Caption", "Published", "Category"
		e = rows.Scan(
			&m.Link,
			&m.Caption,
			&m.Published,
			&m.Category,
		)
		if e != nil {
			logger.Println("ERROR", e)
			return e
		}

		m.Rows = append(m.Rows, material{
			Link:      m.Link,
			Caption:   m.Caption,
			Published: m.Published,
			Category:  m.Category,
		})
	}

	return nil
}

func (m *material) SelectSource() error {
	stmt, ok := queries["Select#Material#Source"]
	if !ok {
		return errors.New("Запрос Select#Material#Source не найден")
	}

	row := stmt.QueryRow(m.Link)

	e := row.Scan(&m.Source, &m.SourceImages, &m.Category, &m.Caption)
	if e != nil {
		return e
	}

	return nil
}