daocha_serve/Source/server/ResourcemanagementServer/sqlUtil.js

const tableColumnList = require("./tableColumnConfig.js"); //字典表

// 资源管理查询表中所有数据
function getALLlist(req) {
  let tablename = req.body.tablename;
  let sql = `select *,st_astext(geom) from ${tablename} order by id asc`;
  return sql;
}

function addSourceimage(req) {
  // console.log(req.body);
  let tname = req.body.tbname;
  let route = req.body.route;
  // console.log(route);
  let layer = req.body.layer;
  let name = req.body.name;
  let allroute = route.map((item) => {
    return item.route;
  });
  let sql = `INSERT INTO  ${tname} (layer,name,route) values ('${layer}','${name}','${allroute}')`;
  // console.log(sql);
  return sql;
}
// 根据名称进行查询
function querynameServerSql(req) {
  let tablename = req.body.tablename;
  let searchmsg = req.body.searchmsg;
  let sql = `select * from ${tablename} where name like '%${searchmsg}%'`;
  return sql;
}
// 批量删除
function batchremove(tablename, fields) {
  var key;
  var value = [];
  if (!tablename) return;
  for (var i in fields) {
    key = i;
    fields[i].forEach((item) => {
      value.push("'" + item + "'");
    });
  }
  let sql = "delete from " + tablename + " where " + key + " in ";
  sql += `(${value})`;
  return sql;
}
function getPoint(req) {
  let tablename = req.body.tablename;
  let name = req.body.name;
  let id = req.body.id;
  let sql = `select st_x(geom) AS lon,st_y(geom) AS lat from ${tablename} where  id = ${id} and  name = '${name}'`;
  // console.log(sql);

  return sql;
}
function getEditlist(req) {
  let tablename = req.body.tablename;
  let id = req.body.id;
  let name = req.body.name;
  let sql = `select * from ${tablename} where id = ${id} and name = '${name}'`;
  // console.log(sql);
  return sql;
}
function editchange(req) {
  // console.log(req.body);
  let tablename = req.body.tablename;
  let id = req.body.id;
  let allnamearr = req.body.prop_propnamearr;
  // console.log(allnamearr);
  let a = "";
  let b = "";
  // let alltablelist = tableColumnList.list; //拿到所有的表头名字
  let alltablelist = tableColumnList.list; //拿到所有的表头名字
  for (var i = 0; i < allnamearr.length; i++) {
    for (var j = 0; j < alltablelist.length; j++) {
      if (allnamearr[i].propname == alltablelist[j].propname) {
        a += '"' + alltablelist[j].prop + '"' + ",";
        b += "'" + allnamearr[i].prop + "'" + ",";
      }
    }
  }
  let A = a.slice(0, a.length - 1);
  let B = b.slice(0, b.length - 1);
  // console.log(A);
  let sql = `update ${tablename} set ${A}= ${B} where id = ${id}`;
  // console.log(sql);
  return sql;
}
function selectsearch(req) {
  let tablename = req.body.tablename;
  let selectboxarr = req.body.selectboxarr;
  let inputarr = req.body.inputarr;
  // let inputArr = inputarr.filter(function (s) {
  //   return s && s.trim();
  // })
  // console.log(inputArr);
  // console.log(inputarr);
  let str = "";
  // let alltablelist = tableColumnList.list; //拿到所有的表头名字
  for (var i = 0; i < selectboxarr.length; i++) {
    for (var j = 0; j < inputarr.length; j++) {
      if (i == j) {
        str += '"' + selectboxarr[j] + '"like' + "'%" + inputarr[i] + "%' AND ";
      }
    }
  }
  str = str.slice(0, str.length - 5);
  // console.log(str);
  let sql = `select *from ${tablename} where ${str} order by id asc`;
  // console.log(sql);
  return sql;
}
function sourcedetele(req) {
  let tablename = req.body.tablename;
  let id = req.body.id;
  let name = req.body.name;
  let sql = `DELETE FROM ${tablename} WHERE id = ${id} AND name = '${name}';`;
  // console.log(sql);
  return sql;
}

function likeSearch(req) {
  // console.log(req.body);

  let tablename = req.body.tablename;
  let name = req.body.name;

  let sql = `select *from ${tablename} WHERE type  like '%${name}%';`;
  // console.log(sql);
  return sql;
}

module.exports = {
  getALLlist,
  addSourceimage,
  querynameServerSql,
  batchremove,
  getPoint,
  getEditlist,
  editchange,
  selectsearch,
  sourcedetele,
  likeSearch,
};