hfysite/server/database/d_product.js

const mysql = require('./mysql');
const {searchSql,limitSql} = require("../tools/searchSql");
const time = require("../tools/time_cjs");
const log = require("../logger").logger("d_product","info")
function loadProducts(key, page, limit) {
  let sql = ``;
  let values = [];
  sql += `SELECT
            p.proid as id,p.remark,p.name,p.image,p.source,p.sourceType,
            p_type.type_name
          FROM
            hfy_product as p ,
            hfy_product_type as p_type
          WHERE
            p.type_id = p_type.type_id
            and p_type.type_key = ?`;
  values = [key];
  sql += ` ORDER BY p.sort DESC, p.add_time DESC `;
  let _limitSql = limitSql(limit,page);
  sql += _limitSql.sql;
  // 添加排序
  values.push(..._limitSql.values);
  return mysql.pq(sql, values);
}

function getProductInfo(id) {
  let sql = ``;
  let values = [];
  sql += `SELECT
              p.*,
              p_type.type_key
          FROM
            hfy_product as p,
            hfy_product_type as p_type
          WHERE
            p.type_id = p_type.type_id
            and p.proid = ?`;
  values = [id];
  return mysql.pq(sql, values);
}
function addProduct(data, type_id) {
  let sql = ``;
  let values = [];
  sql += `INSERT INTO hfy_product (
            type_id,
            name,
            remark,
            sort,
            seo_key,
            image,
            sub_img,
            detail,
            overview,
            parameter,
            add_time) VALUES
            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  values.push(type_id);
  values.push(data.name);
  values.push(data.remark);
  values.push(data.sort);
  values.push(data.seo_key);
  values.push(data.image);
  values.push(data.sub_img);
  values.push(data.detail);
  values.push(data.overview);
  values.push(data.parameter);
  values.push(time.getUnixTimeStamp());
  return mysql.pq(sql, values);
}

function editProduct(data, type_id, id) {
  let sql = ``;
  let values = [];
  sql += `UPDATE hfy_product SET
            type_id = ?,
            name = ?,
            remark = ?,
            sort = ?,
            seo_key = ?,
            image = ?,
            sub_img = ?,
            detail = ?,
            overview = ?,
            parameter = ?
          WHERE
            proid = ?`;
  values.push(type_id);
  values.push(data.name);
  values.push(data.remark);
  values.push(data.sort);
  values.push(data.seo_key);
  values.push(data.image);
  values.push(data.sub_img);
  values.push(data.detail);
  values.push(data.overview);
  values.push(data.parameter);
  values.push(id);
  return mysql.pq(sql, values);
}

function deleteProduct(id) {
  let sql = ``;
  let values = [];
  sql += `DELETE FROM hfy_product WHERE proid = ? limit 1`;
  values = [id];
  return mysql.pq(sql, values);
}

function getSubImages(id) {
  let sql = ``;
  let values = [];
  sql += `SELECT
              id, path
          FROM
            hfy_product_images
          WHERE
            proid = ?`;
  values = [id];
  return mysql.pq(sql, values);
}

function searchProducts(type='array',searchParam,sort,page,limit){
  let sql = ``;
  let values = [];
  if(type === 'count'){
    sql = `select count(*) as total `;
  }else{
    sql = `select
    p.proid as id,
    p.remark,p.name,
    p.image,p.source,
    p.sourceType,
    p_type.type_key
    `;
  }
  sql += `
   from
   hfy_product as p,
   hfy_product_type as p_type
  `
  sql += `where p.type_id = p_type.type_id`
  if(searchParam.key){
    sql += ` and p.name like '%${searchParam.key}%'`
  }
  if(searchParam.type){
    sql += ` and p_type.type_key = ?`
    values.push(searchParam.type)
  }
  sql += ` ORDER BY p.sort DESC, p.add_time DESC `;
  return searchSql(mysql.pq, type, sql, values, limit, page);
}

/**
 * 搜索产品,只返回id和name等信息,节约流量
 * @param type
 * @param searchParam
 * @param page
 * @param limit
 * @returns {*}
 */
function searchProductsByMini(type='array',searchParam,sort,page,limit){
  let sql = ``;
  let values = [];
  if(type === 'count'){
    sql = `select count(*) as total `;
  }else{
    sql = `select
    p.proid as id,
    p.name,
    p_type.type_key
    `;
  }
  sql += `
   from
   hfy_product as p,
   hfy_product_type as p_type
  `
  sql += `where p.type_id = p_type.type_id`
  if(searchParam.key){
    sql += ` and p.name like '%${searchParam.key}%'`
  }
  if(searchParam.type){
    sql += ` and p_type.type_key = ?`
    values.push(searchParam.type)
  }
  sql += ` ORDER BY p.sort DESC, p.add_time DESC `;
  return searchSql(mysql.pq,type,sql,values,limit,page);
}

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

function getProductById(id){
  let sql = `SELECT *,name as title FROM hfy_product WHERE proid = ? limit 1`;
  return mysql.pq(sql,[id]);
}

function getProductByTypeId(id){
  let sql = `SELECT * FROM hfy_product WHERE type_id = ?`;
  return mysql.pq(sql,[id]);
}

// 获取产品类型列表
function getProductTypeList() {
  let sql = `SELECT * FROM hfy_product_type ORDER BY type_sort DESC`;
  return mysql.pq(sql, []);
}

// 编辑产品类型
function editProductType(id, typeChange) {
  let sql = ``
  let values = [];
  sql += `UPDATE hfy_product_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.sub_text){
    sql += `,sub_text = ?`;
    values.push(typeChange.sub_text);
  }
  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.shop_addr){
    sql += `,shop_addr = ?`;
    values.push(typeChange.shop_addr);
  }
  if(typeChange.seo_key){
    sql += `,seo_key = ?`;
    values.push(typeChange.seo_key);
  }
  sql += ` WHERE type_id = ?`;
  values.push(id);
  return mysql.pq(sql, values);
}
// 删除产品类型
function deleteProductType(id) {
  let sql = `DELETE FROM hfy_product_type WHERE type_id = ?`;
  return mysql.pq(sql, [id]);
}

// 新增产品类型
function addProductType(type) {
  let sql = `INSERT INTO hfy_product_type
(date_time, type_name, type_key, type_sort, type_logo, seo_key, sub_text)
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);
  return mysql.pq(sql, values);
}

// 获取类型
function getTypeByKey(key) {
  let sql = `SELECT * FROM hfy_product_type WHERE type_key = ?`;
  return mysql.pq(sql, [key]);
}

module.exports = {
  loadProducts,
  getProductInfo,
  addProduct,
  editProduct,
  deleteProduct,
  getSubImages,
  searchProducts,
  searchProductsByMini,
  loadTypes,
  getProductByTypeId,
  getProductById,
  getProductTypeList,
  editProductType,
  deleteProductType,
  addProductType,
  getTypeByKey
}