hfysite/server/database/d_base.js

const mysql = require('./mysql');
const {searchSql,limitSql} = require("../tools/searchSql");
const {isEmpty} = require("../tools/typeTool_cjs");
const {db_base} = require("../map/dbField");
const log = require("../logger").logger("d_base","info");

/**
 * table carousel
 * id 主键
 * sort 排序
 * fileId 图片对应的id
 * type 1: 文章 2: 产品 3: 直接链接
 * link 文章id 产品id 链接
 * value 根据不同的类型的直接值, 会再前端进行转换
 * valueShowText 值用于提示的文字
 * title 标题, 用于更自定义显示
 * subTitle 副标题
 * showType 显示位置, 用于区分轮播或者展示快的区域类型, 轮播可以多个, 展示快只允许7个
 */

/**
 * 获取轮播图
 * @returns {Promise | Promise<unknown>}
 */
function getCarousel(searchParam = {}){
  let sql = `SELECT c.*,f.filePath,f.fileType,f.fileId
            FROM
                hfy_carousel as c,
                hfy_files as f
            WHERE c.fileId = f.fileId`;
  let values = [];
  sql += ` and c.showType = ${db_base.showType.carousel}`;
  if(!isEmpty(searchParam.state)){
    sql += ` and c.state = ?`;
    values.push(searchParam.state);
  }

  sql += ` order by c.sort asc`
  return mysql.pq(sql,values);
}

// 获取启用的轮播与展示块
async function getEnableCarouselAndShowBlocks(){
  let sql = `SELECT c.*,f.filePath,f.fileType,f.fileId
            FROM
                hfy_carousel as c,
                hfy_files as f
            WHERE c.fileId = f.fileId`;
  let values = [];
  sql += ` and c.state = ?`;
  values.push(db_base.carouselState.enable);
  sql += ` order by c.sort asc`
  return mysql.pq(sql,values)
}


function getCarouselById(id){
  let sql = `SELECT c.*,f.filePath,f.fileType,f.fileId
            FROM
                hfy_carousel as c,
                hfy_files as f
            WHERE
                c.fileId = f.fileId
                and c.id = ?
            `;
  sql += ` and c.showType = ${db_base.showType.carousel}`;

  let values = [id];
  return mysql.pq(sql,values);
}
function addCarousel(sort,fileId,type,value,valueShowText,updateTime){
  let sql = `INSERT INTO hfy_carousel (sort, fileId, type, value, valueShowText, updateTime, showType)
                    VALUES (?,?,?,?,?,?)`;
  return mysql.pq(sql,[sort,fileId,type,value,valueShowText,updateTime, db_base.showType.carousel]);
}
function deleteCarousel(id){
  let sql = `DELETE FROM hfy_carousel WHERE id = ? and showType = ?`;
  return mysql.pq(sql,[id, db_base.showType.carousel]);
}

function updateCarousel(id,updateParam,time){
  let sql = `UPDATE hfy_carousel SET `;
  let values = [];
  if(!isEmpty(updateParam.sort)){
    sql += ` sort = ?,`;
    values.push(updateParam.sort);
  }
  if(updateParam.fileId){
    sql += ` fileId = ?,`;
    values.push(updateParam.fileId);
  }
  if(!isEmpty(updateParam.type)){
    sql += ` type = ?,`;
    values.push(updateParam.type);
  }
  if(updateParam.value && updateParam.valueShowText){
    sql += ` value = ?,`;
    values.push(updateParam.value);
    sql += ` valueShowText = ?,`;
    values.push(updateParam.valueShowText);
  }
  if(!isEmpty(updateParam.state)){
    sql += ` state = ?,`;
    values.push(updateParam.state);
  }
  sql += ` updateTime = ?`;
  values.push(time);
  sql += ` WHERE id = ? and showType = ${db_base.showType.carousel} limit 1`;
  values.push(id);
  console.log(sql);
  console.log(values);
  return mysql.pq(sql,values);
}

// 展示块数据库操作函数
/**
 * 获取展示块数据
 * @returns {Promise | Promise<unknown>}
 */
function getShowBlocks() {
  let sql = `SELECT c.*,f.filePath,f.fileType,f.fileId
            FROM
                hfy_carousel as c,
                hfy_files as f
            WHERE c.fileId = f.fileId`;
  let values = [];
  sql += ` and c.showType = ${db_base.showType.showBlock}`;
  sql += ` order by c.sort asc`
  return mysql.pq(sql, values);
}

/**
 * 根据ID获取展示块
 * @param id 展示块ID
 * @returns {Promise | Promise<unknown>}
 */
function getShowBlockById(id) {
  let sql = `SELECT c.*,f.filePath,f.fileType,f.fileId
            FROM
                hfy_carousel as c,
                hfy_files as f
            WHERE
                c.fileId = f.fileId
                and c.id = ?
            `;
  sql += ` and c.showType = ${db_base.showType.showBlock}`;

  let values = [id];
  return mysql.pq(sql, values);
}

/**
 * 添加展示块
 * @param sort 排序
 * @param fileId 文件ID
 * @param type 类型
 * @param value 值
 * @param valueShowText 显示文本
 * @param title 标题
 * @param subTitle 副标题
 * @param updateTime 更新时间
 * @returns {Promise | Promise<unknown>}
 */
function addShowBlock(sort, fileId, type, value, valueShowText, title, subTitle, updateTime) {
  let sql = `INSERT INTO hfy_carousel (sort, fileId, type, value, valueShowText, title, subTitle updateTime, showType)
                    VALUES (?,?,?,?,?,?,?,?,?)`;
  return mysql.pq(sql, [sort, fileId, type, value, valueShowText, updateTime, title, subTitle, db_base.showType.showBlock]);
}

/**
 * 更新展示块
 * @param id 展示块ID
 * @param updateParam 更新参数
 * @param time 更新时间
 * @returns {Promise | Promise<unknown>}
 */
function updateShowBlock(id, updateParam, time) {
  let sql = `UPDATE hfy_carousel SET `;
  let values = [];
  if (!isEmpty(updateParam.sort)) {
    sql += ` sort = ?,`;
    values.push(updateParam.sort);
  }
  if (updateParam.fileId) {
    sql += ` fileId = ?,`;
    values.push(updateParam.fileId);
  }
  if (!isEmpty(updateParam.type)) {
    sql += ` type = ?,`;
    values.push(updateParam.type);
  }
  if (updateParam.value && updateParam.valueShowText) {
    sql += ` value = ?,`;
    values.push(updateParam.value);
    sql += ` valueShowText = ?,`;
    values.push(updateParam.valueShowText);
  }
  if (!isEmpty(updateParam.state)) {
    sql += ` state = ?,`;
    values.push(updateParam.state);
  }
  if (updateParam.title) {
    sql += ` title = ?,`;
    values.push(updateParam.title);
  }
  if (updateParam.subTitle) {
    sql += ` subTitle = ?,`;
    values.push(updateParam.subTitle);
  }
  sql += ` updateTime = ?`;
  values.push(time);
  sql += ` WHERE id = ? and showType = ${db_base.showType.showBlock} limit 1`;
  values.push(id);
  return mysql.pq(sql, values);
}



function uploadFiles(type,fileNameArr,uploadTime){
  let sql = `INSERT INTO hfy_files (fileType, filePath, uploadTime)
                    VALUES ${fileNameArr.map(f=>`(?,?,?)`).join(',')}`;
  let values = [];
  for(let fileName of fileNameArr){
    values.push(type);
    values.push(fileName);
    values.push(`${uploadTime}`);
  }
  console.log(sql);
  console.log(values);
  return mysql.pq(sql,values);
}

/**
 * 搜索文件数据
 * @param type
 * @param _params
 * @param sort
 * @param p
 * @param l
 * @returns {*}
 */
function loadFiles(type = 'array',_params,sort,p,l){
  let sql = ``;
  let values = [];
  if(isEmpty(_params)){
    _params = {};
  }
  if(type === 'count'){
    sql = `select count(*) as total `;
  }else {
    sql = `select * `;
  }
  sql += `from hfy_files as f
    where 1 = 1
  `
  if(_params.key){
    sql += ` and ( f.name like '%${_params.key}% or f.tags like '%${_params.key}%' )`
  }
  if(_params.type){
    sql += ` and f.fileType = ? `;
    values.push(_params.type - 1);
  }
  sql += ` order by f.uploadTime desc `;
  return searchSql(mysql.pq,type,sql,values,l,p);
}

function getFileById(fileId){
  let sql = `select * from hfy_files where fileId = ?`;
  return mysql.pq(sql,[fileId]);
}

function deleteFile(fileId){
  let sql = `delete from hfy_files where fileId = ? limit 1`;
  return mysql.pq(sql,[fileId]);
}

function getBaseInfo(id){
  let sql = `select * from hfy_basic`;
  if(id){
    sql += ` where id = ? limit 1`;
    return mysql.pq(sql,[id]);
  }
  return mysql.pq(sql);
}

function editBaseInfo(updateParam, baseId){
  let sql = `update hfy_basic set `;
  let values = [];
  if(!isEmpty(updateParam.company)){
    sql += ` company = ?,`;
    values.push(updateParam.company);
  }
  if(!isEmpty(updateParam.wx_qrc)){
    sql += ` wx_qrc = ?,`;
    values.push(updateParam.wx_qrc);
  }
  if(!isEmpty(updateParam.addr)){
    sql += ` addr = ?,`;
    values.push(updateParam.addr);
  }
  // 网店地址
  if(!isEmpty(updateParam.shop_addr)){
    sql += ` shop_addr = ?,`;
    values.push(updateParam.shop_addr);
  }
  if(!isEmpty(updateParam.tel)){
    sql += ` tel = ?,`;
    values.push(updateParam.tel);
  }
  if(!isEmpty(updateParam.fax)){
    sql += ` fax = ?,`;
    values.push(updateParam.fax);
  }
  if(!isEmpty(updateParam.email)){
    sql += ` email = ?,`;
    values.push(updateParam.email);
  }
  if(values.length === 0){
    return Promise.reject('没有修改任何数据')
  }
  // 尝试移除最后一个逗号
  sql = sql.substring(0,sql.length - 1);
  sql += ` where id = ? limit 1`
  values.push(baseId);
  return mysql.pq(sql,values);
}

function addBaseInfo(data){
  let sql = `insert into hfy_basic (wx_qrc, addr, tel, fax, email, shop_addr)
                    values (?,?,?,?,?)`;
  let {wx_qrc, addr, tel, fax, email, shop_addr} = data;
  shop_addr = shop_addr || '';
  return mysql.pq(sql,[wx_qrc,addr,tel,fax,email, shop_addr]);
}


module.exports = {
  getCarousel,
  getCarouselById,
  addCarousel,
  deleteCarousel,
  updateCarousel,
  getShowBlocks,
  getShowBlockById,
  addShowBlock,
  updateShowBlock,
  getEnableCarouselAndShowBlocks,
  uploadFiles,
  loadFiles,
  getFileById,
  deleteFile,
  getBaseInfo,
  editBaseInfo,
  addBaseInfo
}