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];
  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 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)
  }
  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)
  }
  return searchSql(mysql.pq,type,sql,values,limit,page);
}

function loadTypes() {
  let sql = `SELECT * FROM hfy_product_type`;
  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`;
  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.type_sort){
    sql += `,type_sort = ?`;
    values.push(typeChange.type_sort);
  }
  if(typeChange.type_logo){
    sql += `,type_logo = ?`;
    values.push(typeChange.type_logo);
  }
  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) VALUES (?, ?, ?, ?, ?)`;
  return mysql.pq(sql, [time.getUnixTimeStamp(), type.type_name, type.type_key, type.type_sort, type.type_logo]);
}

module.exports = {
  loadProducts,
  getProductInfo,
  searchProducts,
  searchProductsByMini,
  loadTypes,
  getProductByTypeId,
  getProductById,
  getProductTypeList,
  editProductType,
  deleteProductType,
  addProductType
}