hfysite/server/database/d_product.js

const mysql = require('./mysql');
const {searchSql,limitSql} = require("../tools/searchSql");
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]);
}
module.exports = {
  loadProducts,
  getProductInfo,
  searchProducts,
  searchProductsByMini,
  loadTypes,
  getProductById
}