daocha_serve/Source/server/UserinformationServer/sqlUtil.js

function getuserlist(req) {
  let tablename = req.body.tablename;
  let sql = `select * from ${tablename} order by id asc`;
  return sql;
}

// 查询pdf
function searchpdf(req) {
  let tablename = req.body.tablename;
  let name = req.body.name;
  let date = req.body.date;
  let type = req.body.type;
  let sql = `select *,to_char(date,'yyyy-mm-dd') from ${tablename}  where name = '${name}' and type ='${type}' and date = '${date}'  `;
  console.log(sql);

  return sql;
}

// 预案查询
function yuanlikeSearch(req) {
  let tablename = req.body.tablename;
  // let date = req.body.date;
  let name = req.body.name;

  // let sql = `select *,to_char(date,'yyyy-mm-dd hh24:mi:ss') from ${tablename} order by id asc`;
  // if (date == "") {
  let  sql = `select * from ${tablename}  where   name like '%${name}%' `;
  // } else {
    // let sql = `select * from ${tablename}  where  date ='${date}'  and name like '%${name}%' order by date asc`;
  // }
  console.log(sql);
  return sql;
}

// 预案查询
function duobiaoList(req) {
  let tablename1 = req.body.tablename1;
  let tablename2 = req.body.tablename2;
  // let name = req.body.name;
  // let sql = `select *,to_char(date,'yyyy-mm-dd hh24:mi:ss') from ${tablename} order by id asc`;
  let sql = `select * from ${tablename1}, ${tablename2}  where ${tablename1}.name =${tablename2}.name`;

  return sql;
}

// 预案查询
function duobiaoSearch(req) {
  let tablename1 = req.body.tablename1;
  let tablename2 = req.body.tablename2;
  let name = req.body.name;
  let date = req.body.date;
  // let name = req.body.name;
  // let sql = `select *,to_char(date,'yyyy-mm-dd hh24:mi:ss') from ${tablename} order by id asc`;
  let sql = "";
  if (date == "") {
    sql = `with temp as (
            select * from t_emergencyplan where name like '%${name}%'  
            )
               select  * from    t_siweidaotu  join  temp on  temp.name=t_siweidaotu.name`;
  } else {
    sql = `with temp as (
            select * from t_emergencyplan where name like '%${name}%' and date ='${date}'
            )
               select  * from   t_siweidaotu  join  temp on  temp.name=t_siweidaotu.name`;
  }

  console.log(sql);

  return sql;
}

function getshijianlist(req) {
  let tablename = req.body.tablename;
  let type = req.body.type;

  // let sql = `select *,to_char(date,'yyyy-mm-dd hh24:mi:ss') from ${tablename} order by id asc`;
  let sql = `select *,to_char(date,'yyyy-mm-dd') from ${tablename}  where type = '${type}' order by id asc`;

  return sql;
}

function searchshijianlist(req) {
  let tablename = req.body.tablename;
  let startTime = req.body.startTime;
  let endTime = req.body.endTime;
  let type = req.body.type;
  // console.log(startTime);
  // console.log(endTime);
  let sql = `select *,to_char(date,'yyyy-mm-dd') from ${tablename} where date >='${startTime}' and date<='${endTime}' and type = '${type}'  order by id asc `;
  return sql;
}

function searchshijian(req) {
  let tablename = req.body.tablename;
  let startTime = req.body.startTime;
  let endTime = req.body.endTime;
  let name = req.body.name;
  let type = req.body.type;
  // console.log(startTime);
  // console.log(endTime);
  let sql = `select *,to_char(date,'yyyy-mm-dd') from ${tablename} where date >='${startTime}' and date<='${endTime}'and name like '%${name}%' and  type = '${type}'  order by id asc`;
  return sql;
}

function searchshigu(req) {
  let tablename = req.body.tablename;
  let startTime = req.body.startTime;
  let endTime = req.body.endTime;
  let name = req.body.name;

  console.log(startTime);
  console.log(endTime);
  let sql = `select * from ${tablename} where date >='${startTime}' and date <= '${endTime}'and name like '%${name}%'  order by id asc`;
  console.log(sql);

  return sql;
}

function searchshigu2(req) {
  let tablename = req.body.tablename;
  let startTime = req.body.startTime;
  let endTime = req.body.endTime;
  let type = req.body.type;

  console.log(startTime);
  console.log(endTime);
  let sql = `select * from ${tablename} where date >='${startTime}' and date <= '${endTime}'and type like '%${type}%'  order by id asc`;
  console.log(sql);

  return sql;
}

function deleteuserlist(req) {
  let tablename = req.body.tablename;
  let id = req.body.id;
  let sql = `DELETE FROM ${tablename}
         WHERE id = '${id}' ;`;
  return sql;
}
// function deleteuserlist(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})`;
//     console.log(sql);

//     return sql
// }

// 添加
function addUser(tablename, fields) {
  if (!tablename) return;
  let sql = "insert into " + tablename + "(";
  var field = [];
  var value = [];
  for (var i in fields) {
    field.push(i);
    value.push(fields[i]);
  }
  sql += field.join(",") + ") values ('" + value.join("','") + "')";
  return sql;
}

// 改
//@param tablename 数据表名称
//@param keyvalue 关键值判断是哪一行相当于id
//@param fields 条件字段，json格式
function modifyUser(req) {
  // console.log(req.body);
  let tablename = req.body.tablename;
  let id = req.body.id;
  let name = req.body.name;
  let type = req.body.type;
  let password = req.body.password;
  let power = req.body.power;
  let sql = `update ${tablename} set name = '${name}',type = '${type}',password = '${password}',power = '${power}' where id = ${id}`;
  return sql;
}

// 通用事故模块xwj
function likeselectshigu(tbname, likefields, timefields) {
  if (!tbname) return;
  var str = "select *,st_astext(geom) from " + tbname + " where ";

  if (Object.keys(timefields).length != 0) {
    str +=
      "date between '" +
      timefields.starttime +
      "' and '" +
      timefields.endtime +
      "' and ";
  }
  if (Object.keys(likefields).length != 0) {
    for (var i in likefields) {
      str += i + " LIKE " + "'%" + likefields[i] + "%'" + " and ";
    }
  }
  str = str.substring(0, str.length - 4);

  return str;
}
module.exports = {
  getuserlist,
  deleteuserlist,
  addUser,
  modifyUser,
  getshijianlist,
  searchshijianlist,
  searchshijian,
  searchpdf,
  yuanlikeSearch,
  searchshigu,
  searchshigu2,
  duobiaoList,
  duobiaoSearch,

  // xwj事故定位模块
  likeselectshigu,
};