daocha_serve/Source/server/UserDailyServer/sqlUtil.js

function getuserlist(req) {
  let tablename = req.body.tablename;
  let sql = `select * from ${tablename} order by id asc`;
  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 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 addDaily(req) {
  let fields = req.body.conditionFields;
  let tablename = req.body.tablename;
  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("','") + "')";
  console.log(sql);
  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;
}
module.exports = {
  getuserlist,
  deleteuserlist,
  addDaily,
  modifyUser,
  getshijianlist,
  searchshijianlist,
  searchshijian,
};