daocha_serve/Source/server/jiance-echartServer/util/sqlUtil.js

// 查询表内所有数据
function listTableByName(req) {
  let tbName = req.body.tbName;
  let sql = `select chuijia from ${tbName} `;
  console.log(sql);
  return sql;
}
// 新增某些数据
function addSome(req) {
  let tbName = req.body.tbName;
  let sql = `INSERT INTO ${tbName} id,xianming VALUES (?,?) `;
  console.log(sql);
  return sql;
}
// 删除某些记录
function deleteSome(req) {
  let tbName = req.body.tbName;
  let sql = `delete from ${tbName} where <condition> `;
  console.log(sql);
  return sql;
}
// 更新某些记录
function updateSome(req) {
  let tbName = req.body.tbName;
  let sql = `update ${tbName} set <id=?>  where <condition> `;
  console.log(sql);
  return sql;
}

// 查询表内所有数据
function wyc(req) {
  let tbName = req.body.tbName;
  let sql = `select * from ${tbName} `;
  console.log(sql);
  return sql;
}
// 查询表内所有数据
function dwjc(req) {
  let tbName = req.body.tbName;
  let sql = `select * from ${tbName} `;
  console.log(sql);
  return sql;
}
// 查询表内所有数据
function dcpf(req) {
  let tbName = req.body.tbName;
  let sql = `select * from ${tbName} `;
  console.log(sql);
  return sql;
}
// 查询表内所有数据
function gdjh(req) {
  let tbName = req.body.tbName;
  let sql = `select * from ${tbName} `;
  console.log(sql);
  return sql;
}
//查询接触网表内非接触导高12
function fjcdg1(req) {
  let tbName = req.body.tbName;
  let sql = `select fjcdg1 from ${tbName} `;
  console.log(sql);
  return sql;
}

function fjcdg2(req) {
  let tbName = req.body.tbName;
  let sql = `select fjcdg2 from ${tbName} `;
  console.log(sql);
  return sql;
}

function licheng(req) {
  let tbName = req.body.tbName;
  let sql = `select sudu from ${tbName} `;
  console.log(sql);
  return sql;
}

function jtjc(req) {
  let tbName = req.body.tbName;
  let sql = `select zqpm from ${tbName} `;
  console.log(sql);
  return sql;
}

function czy(req) {
  let tbName = req.body.tbName;
  let sql = `select cjz from ${tbName} `;
  console.log(sql);
  return sql;
}

function jtjclc(req) {
  let tbName = req.body.tbName;
  let sql = `select sjlc from ${tbName} `;
  console.log(sql);
  return sql;
}

function czylc(req) {
  let tbName = req.body.tbName;
  let sql = `select licehng from ${tbName} `;
  console.log(sql);
  return sql;
}

function bxlc(req) {
  let tbName = req.body.tbName;
  let sql = `select glb from ${tbName} `;
  console.log(sql);
  return sql;
}

function yuzhixiugai(req) {
  let tbName = req.body.tbName;
  let sql = `select id,xljb,tqi,tqibh,gaodi,gaodibh,guixiang,shuiping,shuipingbh,sanjg,sanjgbh,huangche from ${tbName} `;
  console.log(sql);
  return sql;
}
function yuzhixiugai2(req) {
  let tbName = req.body.tbName;
  let sql = `select * from ${tbName} `;
  console.log(sql);
  return sql;
}

function updateyuzhi(req) {
  let tbName = req.body.tbName;
  let tqi = req.body.tqi;
  let id = req.body.id;
  let tqibh = req.body.tqibh;
  let gdyz = req.body.gdyz;
  let gdbhyz = req.body.gdbhyz;
  let gxyz = req.body.gxyz;
  let spyz = req.body.spyz;
  let spbhyz = req.body.spbhyz;
  let sjkyz = req.body.sjkyz;
  let sjkbhyz = req.body.sjkbhyz;
  let hcyz = req.body.hcyz;
  let sql = `update ${tbName} set tqi= ${tqi} ,tqibh= ${tqibh} ,gaodi= ${gdyz} ,gaodibh= ${gdbhyz} ,guixiang= ${gxyz} , shuiping= ${spyz} ,shuipingbh= ${spbhyz} ,sanjg= ${sjkyz} ,sanjgbh= ${sjkbhyz} ,huangche= ${hcyz} where id= '${id}'`;
  console.log(sql);
  return sql;
}

function updateDwyuzhi(req) {
  let tbName = req.body.tbName;
  let id = req.body.id;
  let passchange = req.body.passchange;
  let change = req.body.change;
  let movechange = req.body.movechange;
  let totalworkmax = req.body.totalworkmax;
  let totalworkmin = req.body.totalworkmin;
  let totalworkavg = req.body.totalworkavg;
  let changeratemax = req.body.changeratemax;
  let changeratemin = req.body.changeratemin;
  let changerateavg = req.body.changerateavg;
  let offsetvaluemax = req.body.offsetvaluemax;
  let offsetvaluemin = req.body.offsetvaluemin;
  let offsetvalueavg = req.body.offsetvalueavg;
  let powervariancemax = req.body.powervariancemax;
  let powervariancemin = req.body.powervariancemin;
  let powervarianceavg = req.body.powervarianceavg;
  let sql = `update ${tbName} set passchange= ${passchange} ,change= ${change} ,movechange= ${movechange} ,totalworkmax= ${totalworkmax} ,totalworkmin= ${totalworkmin} , totalworkavg= ${totalworkavg} ,changeratemax= ${changeratemax} ,changeratemin= ${changeratemin} ,changerateavg= ${changerateavg} ,offsetvaluemax= ${offsetvaluemax} ,offsetvaluemin= ${offsetvaluemin} ,offsetvalueavg= ${offsetvalueavg} ,powervariancemax= ${powervariancemax} ,powervariancemin= ${powervariancemin} ,powervarianceavg= ${powervarianceavg} where id= '${id}'`;
  console.log(sql);
  return sql;
}

function shaixuanyuzhi(req) {
  let xlm = req.body.xlm;
  let dcbh = req.body.dcbh;
  let czm = req.body.czm;
  let tbName = req.body.tbName;
  // if (xlm!=false && dcbh!=true || czm!=true) {
  //      sql = `select  * from ${tbName} where xlm='${xlm}'  `
  // }
  // else if(dcbh!=false && xlm!=true || czm!=true){
  //      sql = `select  * from ${tbName} where dcbh='${xlm}'  `
  // }
  // else if(czm!=false && xlm!=true || dcbh!=true){
  //      sql = `select  * from ${tbName} where czm='${xlm}'  `
  // }
  let sql = `select  * from ${tbName} where xlm='${xlm}'  or czm='${czm}' or dcbh='${dcbh}' `;

  console.log(sql);
  return sql;
}

// 新增某些数据
function addPoint(req) {
  let tbName = req.body.tbName;
  let dcbh = req.body.dcbh;
  let xlm = req.body.xlm;
  let xljb = req.body.xljb;
  let chezhan = req.body.chezhan;
  let Lon = req.body.Lon;
  let Lat = req.body.Lat;
  let type = "258";
  let tqi = "10";
  let tqibh = "10";
  let gdyz = "10";
  let gdbhyz = "10";
  let gxyz = "10";
  let spyz = "10";
  let spbhyz = "10";
  let sjkyz = "10";
  let sjkbhyz = "10";
  let hcyz = "10";
  let jxf = "0";
  let sql = `INSERT INTO ${tbName} (xlm,czm,dcbh,tqi,tqibh,gaodi,gaodibh,guixiang,shuiping,shuipingbh,sanjg,sanjgbh,huangche,jxf,Lon,Lat,type,xljb) VALUES ('${xlm}','${chezhan}','${dcbh}','${tqi}','${tqibh}','${gdyz}','${gdbhyz}','${gxyz}','${spyz}','${spbhyz}','${sjkyz}','${sjkbhyz}','${hcyz}','${jxf}','${Lon}','${Lat}','${type}','${xljb}') `;
  console.log(sql);
  return sql;
}

// 查询点位表内所有数据
function getPointList(req) {
  let tbName = req.body.tbName;
  let chezhan = req.body.chezhan;
  let sql = `select * from ${tbName} where czm='${chezhan}'`;
  console.log(sql);
  return sql;
}

//通过sql获取报警列表
function getAlertList(req) {
  // let sql = `select a.xlm,a.czm,a.dcbh,b.riqi from t_daocha_account as a,t_jihefenxi_account as b, t_baojing_yuzhi as c, t_point_list as d where a.jgjlc = b.jgjlc and a.xlm = b.xlm and a.dcbh = c.dcbh and c.xljb = d.xljb and (b.gdfz > d.gaodi or b.dcqtqi > d.tqi or b.gdfz > d.gaodi or b.gxfz > d.guixiang or b.spfz > d.shuiping or b.sjkfz > d.sanjg or b.cthjfz > d.huangche)`;
  let sql = `select a.xlm,a.czm,a.dcbh,b.riqi from t_daocha_account as a,t_jihefenxi_account as b, t_point_list_dianwuyuzhi as c, t_point_list as d where a.jgjlc = b.jgjlc and a.xlm = b.xlm and d.xljb ='普速' and (b.gdfz > d.gaodi or b.dcqtqi > d.tqi or b.gdfz > d.gaodi or b.gxfz > d.guixiang or b.spfz > d.shuiping or b.sjkfz > d.sanjg or b.cthjfz > d.huangche)`;
  console.log(sql);
  return sql;
}

function getqkAlertList(req) {
  let sql = `select a.xianlu,a.chezhan,a.dcbh,b.time from t_quekou_account as a, t_qkanalysis_account as b, t_point_list_dianwuyuzhi as d where a.qxxh = b.gapnumber and (b.movechange > d.movechange or b.change > d.change or b.passchange > d.passchange )`;
  return sql;
}

function getglAlertList(req) {
  let sql = `select a.xianlu,a.chezhan,a.dcbh,c.time from t_gonglv_account as a, t_glanalysispro_account as c, t_point_list_dianwuyuzhi as d where a.qxxh = c.curvenumber and ( c.totalworkmax > d.totalworkmax or c.totalworkmin > d.totalworkmin or c.totalworkavg > d.totalworkavg or c.changeratemax > d.changeratemax or c.changeratemin > d.changeratemin or c.changerateavg > d.changerateavg or c.offsetvaluemax > d.offsetvaluemax or c.offsetvaluemin > d.offsetvaluemin or c.offsetvalueavg > d.offsetvalueavg or c.powervariancemax > d.powervariancemax or c.powervariancemin > d.powervariancemin or c.powervarianceavg > d.powervarianceavg)`;
  return sql;
}

//通过sql获取当前点位
function getPointCurrent(req) {
  let dcbh = req.body.dcbh;
  let czm = req.body.czm;
  let sql = `select a.xlm,a.czm,a.dcbh,b.dcqtqi,b.gdfz,b.gxfz,b.spfz,b.sjkfz,b.cthjfz,b.riqi from t_daocha_account as a,t_jihefenxi_account as b where a.jgjlc = b.jgjlc and a.xlm = b.xlm and a.dcbh = '${dcbh}' and a.czm = '${czm}'`;
  console.log(sql);
  return sql;
}


//通过sql获取当前道岔缺口值
function getqkCurrent(req) {
  let dcbh = req.body.dcbh;
  let sql = `select  b.time,b.movechange,b.change,b.passchange from t_quekou_account as a ,t_qkanalysis_account as b where a.qxxh = b.gapnumber and a.dcbh = '${dcbh}'`;
  console.log(sql);
  return sql;
}

//通过sql获取当前道岔缺口值
function getGlCurrent(req) {
  let dcbh = req.body.dcbh;
  let value = req.body.value;
  let sql = `select a.dcbh, b.time,${value} from t_gonglv_account as a ,t_glanalysispro_account as b where a.qxxh = b.curvenumber and a.dcbh = '${dcbh}' `;
  console.log(sql);
  return sql;
}

//通过sql获取当前点位
function getBoXing(req) {
  let dcbh = req.body.dcbh;
  let czm = req.body.czm;
  let riqi = req.body.riqi;
  let sql = `select   c.licheng, c.sjk, c.sp, c.ygd,c.zgd, c.zgx, c.ygx from t_daocha_account as a,t_jihefenxi_account as b , t_jihefenxi_boxing as c where a.jgjlc = b.jgjlc and a.xlm = b.xlm and a.dcbh = '${dcbh}' and a.czm = '${czm}' and b.riqi = '${riqi}' and  CAST(c.id AS numeric)  BETWEEN   CAST((string_to_array(b.yswjm_jhfx,','))[1] AS numeric) and CAST((string_to_array(b.yswjm_jhfx,','))[2] AS numeric) `;
  console.log(sql);
  return sql;
}

//通过sql获取数据管理
function getDataManage(req){
  let startTime = req.body.startTime;
  let endTime = req.body.endTime;
  let xlm = req.body.xlm;
  let chezhan = req.body.chezhan;
  let dcbh = req.body.dcbh;
  let startCondition = startTime ==='' ? '' : `and ab.riqi >='${startTime}'`;
  let endCondition = endTime ===''? '' : `and ab.riqi <='${endTime}' `;
  let xlmCondition = xlm ===''? '' : `and ab.xlm = '${xlm}'`;
  let czmCondition = chezhan ===''? '' : `and dc.czm = '${chezhan}'`;
  let dcbhCondition = dcbh ===''? '' : `and dc.dcbh = '${dcbh}'`;
  let sql = `select dc.czbh,dc.zxsd,dc.cxsd,dc.guixing,dc.zyk,dc.juming,dc.jbh,dc.id,dc.dcth,dc.quanchang,dc.czm,dc.dcbh,dc.zch,dc.xgbz,dc.fuzhu,dc.xbh,ab.* from( select lgl.jcrq,lgl.lgcxl,lgl.lghxl,lgl.tgxs,lgl.yswjm,lgl.lzlfz,lgl.lzjzl,lgl.zzcfz,lgl.yzhfz,lgl.yzcfz,lgl.ytgxs,lgl.ztgxs,lgl.id as lgl_id,lgl.zzhfz,lgl.sudu_lgl, jhfx.xlm,jhfx.yswjm_jhfx,jhfx.gxfz,jhfx.xingbie,jhfx.gjhy,jhfx.gjhyffz,jhfx.gdfz,jhfx.cbgdfz,jhfx.cbgxfz,jhfx.id as jhfx_id,jhfx.jgjlc,jhfx.cthjfz,jhfx.cthjffz,jhfx.ctcjfz,jhfx.ctcjffz,jhfx.spfz,jhfx.zcx,jhfx.riqi,jhfx.zfx,jhfx.dcqtqi,jhfx.jcc,jhfx.remark,jhfx.zgdfz,jhfx.zgxfz,jhfx.ygxfz,jhfx.cbzgdfz,jhfx.cbygdfz,jhfx.cbzgxfz,jhfx.cbygxfz,jhfx.zgjffz_jhfx,jhfx.ygdfz,jhfx.ygjffz,jhfx.xuhao,jhfx.sjkfz,jhfx.ygjfz,jhfx.sudu_jhfx,jhfx.wx_xgjlc,jhfx.wx_bhl_gd,jhfx.wx_bhl_gx,jhfx.wx_bhl_sp,jhfx.wx_bhl_sjk,jhfx.wx_bhl_gj,jhfx.wx_check,jhfx.wx_isload  from t_lunguili_account lgl, t_jihefenxi_account jhfx) as ab,t_daocha_account dc where ab.xlm = dc.xlm and   ab.xingbie = dc.xingbie and  ab.jgjlc = dc.jgjlc ${startCondition}  ${endCondition} ${xlmCondition} ${czmCondition} ${dcbhCondition}`
  return sql
}

//通过sql获取电务功率数据列表
function getDwDataManage(req){
  let startTime = req.body.startTime;
  let endTime = req.body.endTime;
  let xlm = req.body.xlm;
  let chezhan = req.body.chezhan;
  let dcbh = req.body.dcbh;
  let startCondition = startTime ==='' ? '' : `and ab.riqi >='${startTime}'`;
  let endCondition = endTime ===''? '' : `and ab.riqi <='${endTime}' `;
  let xlmCondition = xlm ===''? '' : `and ab.xlm = '${xlm}'`;
  let czmCondition = chezhan ===''? '' : `and dc.czm = '${chezhan}'`;
  let dcbhCondition = dcbh ===''? '' : `and dc.dcbh = '${dcbh}'`;
 
  let sql = `select a.xianlu,a.chezhan,a.dcbh,b.time,b.directionalmarker,b.curvetype,b.totalwork,b.changerate,b.offsetvalue,b.powervariance from t_gonglv_account as a ,t_glanalysis_account as b where a.qxxh = b.curvenumber ${startCondition}  ${endCondition} ${xlmCondition} ${czmCondition} ${dcbhCondition}`
  return sql;
}

//通过sql获取电务缺口数据列表
function getQkDataManage(req){
  let startTime = req.body.startTime;
  let endTime = req.body.endTime;
  let xlm = req.body.xlm;
  let chezhan = req.body.chezhan;
  let dcbh = req.body.dcbh;
  let startCondition = startTime ==='' ? '' : `and ab.riqi >='${startTime}'`;
  let endCondition = endTime ===''? '' : `and ab.riqi <='${endTime}' `;
  let xlmCondition = xlm ===''? '' : `and ab.xlm = '${xlm}'`;
  let czmCondition = chezhan ===''? '' : `and dc.czm = '${chezhan}'`;
  let dcbhCondition = dcbh ===''? '' : `and dc.dcbh = '${dcbh}'`;
 
  let sql = `select a.xianlu,a.chezhan,a.dcbh,a.zzj, b.time,b.movechange,b.change,b.passchange from t_quekou_account as a ,t_qkanalysis_account as b where a.qxxh = b.gapnumber ${startCondition}  ${endCondition} ${xlmCondition} ${czmCondition} ${dcbhCondition}`
  return sql;
}




module.exports = {
  listTableByName,
  wyc,
  dwjc,
  fjcdg1,
  fjcdg2,
  licheng,
  dcpf,
  gdjh,
  bxlc,
  jtjc,
  czy,
  jtjclc,
  czylc,
  yuzhixiugai,
  yuzhixiugai2,
  updateyuzhi,
  updateDwyuzhi,
  shaixuanyuzhi,
  addPoint,
  getPointList,
  getAlertList,
  getqkAlertList,
  getglAlertList,
  getPointCurrent,
  getqkCurrent,
  getGlCurrent,
  getBoXing,
  getDataManage,
  getDwDataManage,
  getQkDataManage,
};