hfysite/server/database/d_base.js

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

/**
 * table carousel
 * id 主键
 * sort 排序
 * image 图片地址
 * type 1: 文章 2: 产品 3: 直接链接
 * link 文章id 产品id 链接
 */

/**
 * 获取轮播图
 * @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 = [];
  if(!isEmpty(searchParam.state)){
    sql += ` and c.state = ?`;
    values.push(searchParam.state);
  }
  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 = ?
            `;
  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)
                    VALUES (?,?,?,?,?,?)`;
  return mysql.pq(sql,[sort,fileId,type,value,valueShowText,updateTime]);
}
function deleteCarousel(id){
  let sql = `DELETE FROM hfy_carousel WHERE id = ?`;
  return mysql.pq(sql,[id]);
}

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 = ? limit 1`;
  values.push(id);
  console.log(sql);
  console.log(values);
  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,
  uploadFiles,
  loadFiles,
  getFileById,
  deleteFile,
  getBaseInfo,
  editBaseInfo,
  addBaseInfo
}