hfysite/server/database/d_solution.js

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

function loadSolution(key, page, limit) {
  let sql = ``;
  let values = [];
  sql += `SELECT
            news.id,
            news.remark,
            news.title as name,
            news.image,
            news.source_val as source,
            news.sourceType,
            n_type.type_key
          FROM
            hfy_news as news ,
            hfy_news_type as n_type
          WHERE
            news.type_id = n_type.type_id
            and n_type.parent_type = 1
            and n_type.type_key = ?`;
  values = [key];
  let _limitSql = limitSql(limit,page);
  sql += _limitSql.sql;
  values.push(..._limitSql.values);
  // log.info(sql);
  return mysql.pq(sql, values);
}

function searchSolution(type='array', searchParam, sort, page,limit){
  let sql;
  let values = [];
  if(type === 'count'){
    sql = `
      select
        count(*) as total
      FROM hfy_news AS news
          INNER JOIN hfy_news_type AS n_type
          ON news.type_id = n_type.type_id`;
  }else{
    sql = `
      SELECT
        news.id,
        news.remark,
        news.title,
        news.title as name,
        news.image,
        news.coverId,
        news.date_time,
        news.hits,
        f.filePath as coverPath,
        f.fileType as coverType,
        news.source,
        news.sourceType,
        n_type.type_key
      FROM hfy_news AS news
        LEFT JOIN hfy_files as f ON news.coverId = f.fileId
        INNER JOIN hfy_news_type AS n_type ON news.type_id = n_type.type_id
    `;
  }
  sql += ` where 1=1 `
  if(searchParam.parentType) {
    sql += ` and n_type.parent_type = ?`
    values.push(searchParam.parentType);
  }

  if(searchParam.key){
    sql += ` and news.title like '%${searchParam.key}%'`
  }

  if(searchParam.type){
    sql += ` and n_type.type_key = ?`
    values.push(searchParam.type)
  }

  // 增加排序
  if(sort && sort.key && sort.type){
    // key value 转换
    if(sort.key === 'date_time')
    if(sort.key === 'hits'){
      sort.key = 'news.hits';
    }else{
        sort.key = 'news.date_time';
    }

    if(sort.type === 'asc'){
      sort.type = 'asc';
    }else{
      sort.type = 'desc';
    }
    sql += ` order by ${sort.key} ${sort.type}`;
  }else{
    sql += ` order by news.date_time desc`;
  }

  // console.log(sql);
  // console.log(values);
  return searchSql(mysql.pq,type,sql,values,limit,page);
}

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

module.exports = {
  loadSolution,
  searchSolution,
  getSolutionInfo
}