hfysite/server/database/d_news.js

const mysql = require('./mysql');
const {searchSql,limitSql} = require("../tools/searchSql");
const {getUnixTimeStamp} = require("../tools/time_cjs");
const time = require("../tools/time_cjs");
const log = require("../logger").logger("d_news","info");

function addReadNum(id){
   let sql = `UPDATE hfy_news SET hits = hits + 1 WHERE id = ? limit 1`;
   let values = [id];
   return mysql.pq(sql,values);
}

function loadTypes() {
  let sql = `SELECT * FROM hfy_news_type ORDER BY type_sort DESC`;
  return mysql.pq(sql, []);
}

function getTypeByKey(key){
  let sql = `SELECT * FROM hfy_news_type WHERE type_key = ? limit 1`;
  return mysql.pq(sql,[key]);
}
function getTypeById(id){
  let sql = `SELECT * FROM hfy_news_type WHERE type_id = ? limit 1`;
  return mysql.pq(sql,[id]);
}



// 轻量搜索接口
function searchAllNewsMini(type='array',searchParam,sort,page,limit){
  let sql;
  let values = [];
  if(type === 'count'){
    sql = `select count(*) as total `;
  }else{
    sql = `select
    news.id ,
    news.title,
    news.remark ,
    news.author,
    news.image,
    news.date_time ,
    n_type.type_name,
    n_type.type_key`;
  }
  sql += `
   from
   hfy_news as news ,
   hfy_news_type as n_type
  `
  sql += ` where news.type_id = n_type.type_id`
  if(searchParam.key){
    sql += ` and news.title like '%${searchParam.key}%'`
  }
  if(searchParam.type){
    sql += ` and n_type.type_key = ?`
    values.push(searchParam.type)
  }
  sql += ` order by news.sort DESC , news.date_time DESC`
  return searchSql(mysql.pq,type,sql,values,limit,page);
}

function getNewsById(id){
  let sql = `SELECT news.*, n_type.type_key as type_key FROM hfy_news as news, hfy_news_type as n_type WHERE news.type_id = n_type.type_id and id = ? limit 1`;
  return mysql.pq(sql,[id]);
}

function getMiniNewsById(id){
  let sql = `
            SELECT
                news.title, news.id, news.remark, news.author, news.image, news.date_time,
                n_type.type_key as type_key
            FROM
                hfy_news as news,
                hfy_news_type as n_type
            WHERE
                news.type_id = n_type.type_id
            and id = ? limit 1`;
  return mysql.pq(sql,[id]);
}

function getArticleTotalByTypeId(id){
  let sql = `SELECT count(*) as total FROM hfy_news WHERE type_id = ?`;
  return mysql.pq(sql,[id]);
}

function addArticle(article, typeId){
  let sql = `INSERT INTO hfy_news (title, remark, author, content, type_id, image, date_time) VALUES (?, ?, ?, ?, ?, ?, ?)`;
  let values = [];
  values.push(article.title);
  values.push(article.remark);
  values.push(article.author);
  values.push(article.content);
  values.push(typeId);
  values.push(article.cover);
  values.push(getUnixTimeStamp());
  log.info(`[新增文章] sql=${sql}, values=${values}`);
  return mysql.pq(sql,values);
}

function editArticle(article, typeId){
  let sql = `UPDATE hfy_news SET
    title=?, remark=?, author=?, content=?,
    image=?, type_id=?
    WHERE id = ?`;
  let values = [];
  values.push(article.title);
  values.push(article.remark);
  values.push(article.author);
  values.push(article.content);
  values.push(article.cover);
  values.push(typeId);
  values.push(article.id);
  return mysql.pq(sql,values);
}

function delArticle(id){
  let sql = `DELETE FROM hfy_news WHERE id = ? limit 1`;
  return mysql.pq(sql,[id]);
}

// 编辑产品类型
function editArticleType(id, typeChange) {
  let sql = ``
  let values = [];
  sql += `UPDATE hfy_news_type SET date_time = ?`;
  values.push(time.getUnixTimeStamp());
  if(typeChange.type_name){
    sql += `,type_name = ?`;
    values.push(typeChange.type_name);
  }
  if(typeChange.type_key){
    sql += `,type_key = ?`;
    values.push(typeChange.type_key);
  }
  if(typeChange.type_sort){
    sql += `,type_sort = ?`;
    values.push(typeChange.type_sort);
  }
  if(typeChange.type_logo){
    sql += `,type_logo = ?`;
    values.push(typeChange.type_logo);
  }
  if(typeChange.seo_key){
    sql += `,seo_key = ?`;
    values.push(typeChange.seo_key);
  }
  if(typeChange.sub_text){
    sql += `,sub_text = ?`;
    values.push(typeChange.sub_text);
  }
  sql += ` WHERE type_id = ?`;
  values.push(id);
  return mysql.pq(sql, values);
}

// 删除产品类型
function deleteArticleType(id) {
  let sql = `DELETE FROM hfy_news_type WHERE type_id = ?`;
  return mysql.pq(sql, [id]);
}

// 新增产品类型
function addArticleType(type, parentId) {
  let sql =
    `INSERT INTO hfy_news_type (
date_time, type_name, type_key, type_sort, type_logo, seo_key, sub_text, parent_type)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;
  let values = [];
  values.push(time.getUnixTimeStamp());
  values.push(type.type_name);
  values.push(type.type_key);
  values.push(type.type_sort);
  values.push(type.type_logo);
  values.push(type.seo_key);
  values.push(type.sub_text);
  values.push(parentId);

  return mysql.pq(sql,values);
}

module.exports = {
  addReadNum,
  loadTypes,
  searchAllNewsMini,
  getNewsById,
  getMiniNewsById,
  getTypeByKey,
  getTypeById,
  addArticle,
  getArticleTotalByTypeId,
  editArticle,
  delArticle,
  editArticleType,
  deleteArticleType,
  addArticleType,
}