sanwei_server/Source/module/DanTiHua/sql.js

 /* sqlHelper */

 //  //只插入Geo字段
 //  function InsertGeom(req) {
 //     let wkt = req.query.wkt
 //     let tablename = req.query.tablename
 //     let sql = `INSERT INTO public.${tablename}(geom,type) VALUES (st_geomfromtext('${wkt}',4326),'polygon');`

 //      return sql
 //  }

 //插入一整条数据
 function InsertDataWithGeom(req) {
     let type = req.body.type
     let entityname = req.body.entityname
     let jg = req.body.jg
     let collecttime = req.body.collecttime
     let updatetime = req.body.updatetime
     let gd = req.body.gd
     let wkt = req.body.wkt
     let descinfor = req.body.descinfor
         //  let label = req.body.label
     let tablename = req.body.tablename
     let sql = `INSERT INTO public.${tablename}(entityname,jg,collecttime,updatetime,gd,descinfor, geom, type)VALUES ('${entityname}','${jg}','${collecttime}','${updatetime}','${gd}', '${descinfor}', st_geomfromtext('${wkt}',4326), '${type}');`;
     console.log(sql);
     return sql
 }

 //更新一条数据
 function UpdataDataWithGeom(req) {
     let type = req.body.type
     let entityname = req.body.entityname
     let jg = req.body.jg
     let collecttime = req.body.collecttime
     let updatetime = req.body.updatetime
     let gd = req.body.gd
     let wkt = req.body.wkt
     let descinfor = req.body.descinfor
     let id = req.body.id
         //  let label = req.body.label
     let tablename = req.body.tablename;
     let sql = `UPDATE public.${tablename} SET entityname='${entityname}', jg='${jg}', collecttime='${collecttime}', updatetime='${updatetime}', gd='${gd}', descinfor='${descinfor}', geom = st_geomfromtext('${wkt}',4326), type='${type}' where id = ${id}`
     console.log(sql);
     return sql
 }

 //在插入单体化时，查询全部label，防止label重复
 function getAlldata(req) {
     let tablename = req.body.tablename;
     //  let sql = `select label from public.${tablename} ORDER BY id`;
     let sql = `select entityname from public.${tablename} ORDER BY id`;

     if (req.body.getgeom) {
         //  sql = `select id,label,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename}  ORDER BY id`;
         sql = `select id,entityname,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename}  ORDER BY id`;

     }

     return sql
 }

  //获取所有接图表
  function getAllJieTuBiao(req) {
    let tablename = req.body.tablename;
    //  let sql = `select label from public.${tablename} ORDER BY id`;
    let sql = `select* from public.${tablename} ORDER BY id`;

    if (req.body.getgeom) {
        //  sql = `select id,label,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename}  ORDER BY id`;
        sql = `select refname,id,name,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename}  ORDER BY id`;

    }

    return sql
}

 //在插入单体化时，查询全部label，防止label重复
 function getDataByLabel(req) {
     let tablename = req.body.tablename;
     //  let label = req.body.label;
     let entityname = req.body.entityname;
     return `select id, entityname,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename} where  entityname = '${ entityname}'  ORDER BY id`;
 }


 //获取点击位置的实体
 function selectWithPoint(req) {
     let tablename = req.body.tablename;
     let pointwkt = req.body.pointwkt;
     //  let sql = `select label,descinfor,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename} where ST_Intersects(geom,st_geomfromtext('${pointwkt}',4326))  ORDER BY id`
     let sql = `select entityname, jg,collectTime,updateTime,gd,descinfor,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename} where ST_Intersects(geom,st_geomfromtext('${pointwkt}',4326))  ORDER BY id`
     return sql
 }


  //获取点击接图表的实体
  function selectWithJieTuPoint(req) {
    let tablename = req.body.tablename;
    let pointwkt = req.body.pointwkt;
    //  let sql = `select label,descinfor,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename} where ST_Intersects(geom,st_geomfromtext('${pointwkt}',4326))  ORDER BY id`
    let sql = `select id, name, url, st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename} where ST_Intersects(geom,st_geomfromtext('${pointwkt}',4490))  ORDER BY id`
    return sql
}

 //获取点击位置的实体
 function qureyByID(req) {
     let tablename = req.body.tablename;
     let id = req.body.id;
     //  let sql = `select label,descinfor,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename} where id = ${id}  ORDER BY id`
     let sql = `select entityname, jg,collectTime,updateTime,gd,descinfor,st_asgeojson(ST_GeometryN(geom, 1)),st_asgeojson(ST_Centroid(ST_Transform(geom,4326))) as center from public.${tablename} where id = ${id}  ORDER BY id`
     return sql
 }

  //获取点击位置的实体
  function qureyByID(req) {
    let tablename = req.body.tablename;
    let id = req.body.id;
    //  let sql = `select label,descinfor,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename} where id = ${id}  ORDER BY id`
    let sql = `select entityname, jg,collectTime,updateTime,gd,descinfor,st_asgeojson(ST_GeometryN(geom, 1)),st_asgeojson(ST_Centroid(ST_Transform(geom,4326))) as center from public.${tablename} where id = ${id}  ORDER BY id`
    return sql
}

  //获取点击位置的实体
  function qureyByJieTuID(req) {
    let tablename = req.body.tablename;
    let refname = req.body.refname;
    //  let sql = `select label,descinfor,st_asgeojson(ST_GeometryN(geom, 1)) from public.${tablename} where id = ${id}  ORDER BY id`
    let sql = `select name, id,st_asgeojson(ST_GeometryN(geom, 1)),st_asgeojson(ST_Centroid(ST_Transform(geom,4326))) as center from public.${tablename} where refname = ${refname}  ORDER BY name`
    return sql
}

 module.exports = {
     InsertDataWithGeom,
     UpdataDataWithGeom,
     getAlldata,
     selectWithPoint,
     qureyByID,
     getDataByLabel,
     getAllJieTuBiao,
     qureyByJieTuID,
     selectWithJieTuPoint
 };