func FindArticles(offset, size int) ([]entity.Article, int64, error) {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
sql := "SELECT * FROM article WHERE delFlg=0 AND status=0 LIMIT ?,?"
rows, err := dbm.Query(sql, offset, size)
if err != nil {
return nil, 0, err
}
defer rows.Close()
var items []entity.Article = make([]entity.Article, 0)
count := -1
for rows.Next() {
count++
var item entity.Article
rows.Scan(&item.Id, &item.Title, &item.Content, &item.Keywords, &item.Description, &item.Lang, &item.Tag, &item.Timestamp, &item.Status, &item.DelFlg)
tmp, err := strconv.ParseInt(item.Timestamp, 10, 0)
if err != nil {
tmp = time.Now().Unix()
}
item.Timestamp = time.Unix(tmp, 0).Format("2006-01-02")
items = append(items, item)
}
if count == -1 {
return nil, 0, errors.New("no data")
}
return items, GetArticleCount(), nil
}
func SaveArticle(article entity.Article) {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
timestamp := strconv.FormatInt(time.Now().Unix(), 10)
sql := "INSERT INTO article (title,content,keywords,description,lang,tag,timestamp,status,delFlg) VALUES (?,?,?,?,?,?,?,?,?)"
dbm.Exec(sql, article.Title, article.Content, article.Keywords, article.Description, article.Lang, article.Tag, timestamp, article.Status, 0)
}
func FindArticleById(id int) (entity.Article, error) {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
sql := "SELECT * FROM article WHERE id=? AND delFlg=0"
rows, err := dbm.Query(sql, strconv.Itoa(id))
if err != nil {
log.Fatal(err)
}
defer rows.Close()
var item entity.Article
count := -1
for rows.Next() {
count++
rows.Scan(&item.Id, &item.Title, &item.Content, &item.Keywords, &item.Description, &item.Lang, &item.Tag, &item.Timestamp, &item.Status, &item.DelFlg)
tmp, err := strconv.ParseInt(item.Timestamp, 10, 0)
if err != nil {
tmp = time.Now().Unix()
}
// item.Timestamp = time.Unix(tmp, 0).Format("2006-01-02 15:04:05")
item.Timestamp = time.Unix(tmp, 0).Format("2006-01-02")
}
if count == -1 {
return item, errors.New("no data")
}
return item, nil
}
func UpdateArticle(id, title, content string) {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
timestamp := strconv.FormatInt(time.Now().Unix(), 10)
sql := "UPDATE article SET title=?,timestamp=?,content=? WHERE id=?"
dbm.Exec(sql, title, timestamp, content, id)
}
func InstallAction(response http.ResponseWriter, request *http.Request) {
dbm := dao.GetDBM(conf.DATABASE_NAME)
sql := "create table if not exists article (" +
"id integer primary key" +
", title varchar(50)" +
", content text" +
", keywords varchar(100)" +
", description varchar(200)" +
", lang varchar(10)" +
", tag varchar(10)" +
", timestamp varchar(50)" +
", status varchar(5)" +
", delFlg varchar(5)" +
");"
dbm.Exec(sql)
logger.Debug("DB", sql)
sql = "DELETE user"
dbm.Exec(sql)
logger.Debug("DB", sql)
sql = "create table if not exists user (" +
"id integer primary key" +
", first_name varchar(10)" +
", last_name varchar(10)" +
", email varchar(50)" +
", password varchar(200)" +
", status varchar(5)" +
", delFlg varchar(5)" +
");"
dbm.Exec(sql)
logger.Debug("DB", sql)
email := "admin"
password := "******"
hash := md5.New()
hash.Write([]byte(password))
password = hex.EncodeToString(hash.Sum(nil))
sql = "INSERT INTO user (first_name,last_name,email,password,status,delFlg) VALUES (?,?,?,?,?,?)"
dbm.Exec(sql, "admin", "admin", email, password, "0", "0")
logger.Debug("DB", sql)
dbm.Close()
http.Redirect(response, request, "/", http.StatusFound)
}
func GetArticleCount() int64 {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
sql := "SELECT count(*) as count FROM article WHERE delFlg=0 AND status=0"
rows, err := dbm.Query(sql)
if err != nil {
return 0
}
defer rows.Close()
var count int64
count = 0
if err != nil {
return count
}
if rows.Next() {
rows.Scan(&count)
}
return count
}
func Count(table string) int64 {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
sql := "SELECT count(*) as count FROM " + table + " WHERE delFlg=0"
rows, err := dbm.Query(sql)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
var count int64
count = 0
if err != nil {
return count
}
if rows.Next() {
rows.Scan(&count)
}
return count
}
func FindUserByEmail(email string) (entity.User, error) {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
var item entity.User
sql := "SELECT * FROM user WHERE email=?"
rows, err := dbm.Query(sql, email)
if err != nil {
return item, err
}
defer rows.Close()
count := -1
if rows.Next() {
count++
rows.Scan(&item.Id, &item.First_Name, &item.Last_Name, &item.Email, &item.Password, &item.Status, &item.DelFlg)
}
if count == -1 {
return item, errors.New("no data")
}
return item, nil
}
func DeleteArticle(id string) {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
sql := "UPDATE article SET delFlg=? WHERE id=?"
dbm.Exec(sql, 1, id)
}
func DeleteLogical(table, del, id string) {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
sql := "UPDATE " + table + " SET delFlg=? WHERE id=?"
dbm.Exec(sql, del, id)
}
func SaveUser(firstName string, lastName string, email string, password string) {
dbm := dao.GetDBM(conf.DATABASE_NAME)
defer dbm.Close()
sql := "INSERT INTO user (first_name,last_name,email,password) VALUES (?,?,?,?)"
dbm.Exec(sql, firstName, lastName, email, password)
}