var express = require('express');
var router = express.Router();
var command=require('../util/database');
const { all } = require('./loginRouter');

//视频
router.get('/spjk', function (req, res, next) {
    var sqlstr = "select * from monitor where 1=1 ";
    if(req.session.user.gridid){
        sqlstr+=" and gridid='"+req.session.user.gridid+"'";
    }
    command.executesql(sqlstr, (result) => {
        res.json(result);
    });
});
//区域
router.get('/area/:typeid', function (req, res, next) {
    let x=req.params.typeid;
    let sqlstr="select a.*,b.typename,b.extenddata from area a ,area_type b where a.typeid=b.typeid and a.typeid='{0}'";
    if(x=="null"){
        sqlstr="select a.*,b.typename,b.extenddata  from area a ,area_type b where a.typeid=b.typeid";
    }
    if(req.session.user.gridid){
        sqlstr+=" and a.gridid='"+req.session.user.gridid+"'";
    }
    command.executesql(sqlstr.format(x),(result)=>{
        res.json(result);
    });
});
//多字段区域
router.get('/areafield/:typeid', function (req, res, next) {
    let x = req.params.typeid;
    command.executefun("pkg_area.fun_queryarea",{v_typeid:x,v_gridid:(req.session.user.gridid?req.session.user.gridid:"null")},true, (result) => {
        res.json(result);
    });
});
//区域监控
router.get('/areamonitor/:typeid', function (req, res, next) {
    let x=req.params.typeid;
    let sqlstr=`select b.*
    from area_monitor a, monitor b, area c
    where a.monitorid = b.id
    and a.areaid = c.id
     and c.typeid = '{0}'`;
     sqlstr=sqlstr.format(x);
     if(req.session.user.gridid){
        sqlstr+=" and b.gridid='"+req.session.user.gridid+"'";
    }
    command.executesql(sqlstr.format(x),(result)=>{
        res.json(result);
    });
});
//建筑的监控
router.get('/buildingmonitor', function (req, res, next) {
    let sqlstr="select a.* from building_monitor t, monitor a where a.id = t.monitorid";
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});
//小区楼栋
router.get('/building', function (req, res, next) {
    var sqlstr="select id, gridid,housetype, DISTRICTNAME  xqm,name ldh, DISTRICTNAME||name name,type,dy,zdry marktype,zdry color,zljzrq ,wkt ,rooms,unitnum,floornum,roomnum,fydq  from buiding_view where type in('2','1','0','4') {0} order by DISTRICTNAME,name";
    if(req.session.user.gridid){
        sqlstr=sqlstr.format(" and gridid='"+req.session.user.gridid+"'");
    }else{
        sqlstr=sqlstr.format("");
    }
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});
//企业楼栋
router.get('/buildingcompany', function (req, res, next) {
    var sqlstr="select id,gridid,  DISTRICTNAME  xqm,name ldh, DISTRICTNAME||name name ,wkt ,rooms,unitnum,floornum,roomnum from building a where exists( select 1 from HOUSE_COMPANY b where b.buildingid=a.id) and type!='6' {0} order by DISTRICTNAME,name";
    if(req.session.user.gridid){
        sqlstr=sqlstr.format(" and gridid='"+req.session.user.gridid+"'");
    }else{
        sqlstr=sqlstr.format("");
    }
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});

//流动人口
router.get('/ldrk', function (req, res, next) {
    var sqlstr="select id,gridid, housetype,DISTRICTNAME  xqm,name ldh,DISTRICTNAME||name name,type,zljzrq ,wkt ,rooms,unitnum,floornum,roomnum,fydq ,ldls from buiding_view where type!='6' and zljzrq is not null {0}";
    if(req.session.user.gridid){
        sqlstr=sqlstr.format(" and gridid='"+req.session.user.gridid+"'");
    }else{
        sqlstr=sqlstr.format("");
    }
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});

//群租房
router.get('/qzf', function (req, res, next) {
    var sqlstr="select id,gridid, housetype,DISTRICTNAME  xqm,name ldh,DISTRICTNAME||name name,type,zljzrq ,wkt ,rooms,unitnum,floornum,roomnum ,fydq ,ldls from buiding_view where type!='6' and housetype='1'";
    if(req.session.user.gridid){
        sqlstr=sqlstr.format(" and gridid='"+req.session.user.gridid+"'");
    }else{
        sqlstr=sqlstr.format("");
    }
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});

//重点人员
router.get('/zdry/:type', function (req, res, next) {
    let x=req.params.type;
    let sqlstr="select id,gridid, DISTRICTNAME  xqm,name ldh,DISTRICTNAME||name name,type,zdry marktype,zljzrq ,wkt ,rooms,unitnum,floornum,roomnum,zdry color,fydq,ldls ,qsn from buiding_view where type!='6' and zdry is not null {0}";
    if(req.session.user.gridid){
        sqlstr=sqlstr.format(" and gridid='"+req.session.user.gridid+"'");
    }else{
        sqlstr=sqlstr.format("");
    }
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});

//党员
router.get('/party', function (req, res, next) {
    let sqlstr="select t.id,  DISTRICTNAME  xqm,name ldh,t.DISTRICTNAME||t.name||':'||t.xm name,t.x,t.y,t.unitnum,t.floornum,t.roomnum,t.zjhm,t.xm,t.dy,t.ssdz marktype ,'' color from partymember_view t";
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});
//党组
router.get('/party/group', function (req, res, next) {
    let sqlstr="select t.id,  DISTRICTNAME  xqm,name ldh,t.DISTRICTNAME||t.name||':'||t.xm name,t.x,t.y,t.unitnum,t.floornum,t.roomnum,t.zjhm,t.xm,  groupname from partygroup_view t";
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});


//查询6-18岁青少年
router.get('/qsn', function (req, res, next) {
    command.executesql("select  m.ID,m.DISTRICTNAME xqm ,m.NAME ldh,m.DISTRICTNAME || m.NAME ||':'||m.name name, m.X,m.Y,m.UNITNUM,m.FLOORNUM,m.ROOMNUM ,m.ZJHM,m.name xm ,   qsn from QSN_VIEW m", (result) => {
        res.json(result);
    });
});

//查询志愿者
router.get('/zyz', function (req, res, next) {
    command.executesql("select  zjhm,a.name ryname  ,xb,mz,csny ,xjzd ,b.x ,b.y,b.name||' '||a.name name  from (select b.zjhm,a.houseid,c.name,c.xb,c.mz,c.csny ,c.xjzd  from  keypersonal b  ,temporary a,population c  where  a.zjhm=b.zjhm   and  a.zjhm=c.zjhm  and type='12'  union select b.zjhm,a.houseid,c.name,c.xb,c.mz,c.csny ,c.xjzd from  keypersonal b  ,permanent a,population c    where  a.zjhm=b.zjhm  and  a.zjhm=c.zjhm   and type='12'   )a ,(select  x,y ,b.id houseid ,DISTRICTNAME||a.name||b.name name   from building a, house b  where  a.id=b.buildingid)b where a.houseid=b.houseid  union   all   select  b.zjhm,name ryname  ,xb,mz,csny ,xjzd  ,(select to_char(x) from area t where id='34') x,(select to_char(y) from area t where id='34') y,(select name from area t where id='34')||' '||name name from  keypersonal   b ,population c  where  c.zjhm=b.zjhm  and type='12' and b.zjhm not in (select zjhm  from temporary  ) and b.zjhm not in (select zjhm  from  permanent  )", (result) => {
        res.json(result);
    });
});
// //查询防疫人员信息QA
// router.get('/fyry/:type', function (req, res, next) {
//     let x=req.params.type;
//     let sqlstr="select id,gridid, DISTRICTNAME  xqm,name ldh,DISTRICTNAME||name name,type,zdry marktype,zljzrq ,wkt ,rooms,unitnum,floornum,roomnum,zdry color,fydq  from buiding_view a  where type!='3'  and  zdry is not null and  instr(zdry,{0})>0  ";
  
//     command.executesql(sqlstr,(result)=>{
//         res.json(result);
//     });
// });

//重点人员
router.get('/fyry/:type', function (req, res, next) {
    let x=req.params.type;
//根据,截取文件
    let arrtype=x.split(',');
    let sqlstr="select id,gridid, DISTRICTNAME  xqm,name ldh,DISTRICTNAME||name name,type,zdry marktype,zljzrq ,wkt ,rooms,unitnum,floornum,roomnum,zdry color,fydq ,ldls from buiding_view where type!='6' and zdry is not null    ";
    if(arrtype.length>0){
        sqlstr=sqlstr+"and (";
        for(let i=0;i<arrtype.length;i++)
        {
            sqlstr=sqlstr+" instr(zdry,'"+arrtype[i]+"')>0 or ";

        }
        sqlstr= sqlstr.substring(0,sqlstr.lastIndexOf("or"))+")"
    }
    sqlstr= sqlstr+"{0}"
    if(req.session.user.gridid){
        sqlstr=sqlstr.format(" and gridid='"+req.session.user.gridid+"'");
    }else{
        sqlstr=sqlstr.format("");
    }
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});




//防疫人员'
router.get('/queryfyryBybuildingid/:id/:typeid/:foreigntype', function (req, res, next) {
    let x=req.params.id;
    let y=req.params.typeid;
    let foreigntype=req.params.foreigntype;//国外输入类型
    let sqlstr=`select  b.name   hh,d.* 
    from house_view  b, TEMPORARY c, local_zdry_fy d 
    where b.id = c.houseid
    and c.zjhm = d.zjhm and  buildingid='{0}' and d.type='{1}' {2}
    union  
    select  b.name   hh,d.* 
    from house_view  b, permanent c, local_zdry_fy d 
    where b.id = c.houseid
    and c.zjhm = d.zjhm  and  buildingid='{0}' and d.type='{1}' {2} 
   `;
   if(req.session.user.gridid){
        sqlstr=sqlstr.format(x,y," and gridid='"+req.session.user.gridid+"'");
    }else{
        sqlstr=sqlstr.format(x,y,"");
    }
    command.executesql(sqlstr,(result)=>{
     
        let sqlstr1=`select b.name   hh,d.* 
        from house_view  b, TEMPORARY c, (select  a.zjhm,a.name,a.xb ,a.lxdh,a.bz, floor(months_between(sysdate,to_date(substr(a.zjhm,7,8),'yyyy-MM-dd'))/12) age from   population a,
          keypersonal  d where a.zjhm=d.zjhm and  (d.type='{0}' or  d.type='{1}') and  d.zjhm not  in (select  zjhm from  local_zdry_fy  where type='{2}'))d
        where b.id = c.houseid
        and c.zjhm = d.zjhm and  buildingid='{3}' {4}
        union  
        select  b.name   hh,d.* 
        from house_view  b, permanent c,  (select  a.zjhm,a.name,a.xb ,a.lxdh,a.bz,floor(months_between(sysdate,to_date(substr(a.zjhm,7,8),'yyyy-MM-dd'))/12) age from   population a, 
         keypersonal  d where a.zjhm=d.zjhm and  (d.type='{0}' or  d.type='{1}') and  d.zjhm not  in (select  zjhm from  local_zdry_fy where type='{2}'))d
        where b.id = c.houseid
        and c.zjhm = d.zjhm  and  buildingid='{3}' {4}  
       `;
       if(req.session.user.gridid){
        sqlstr1=sqlstr1.format(y,foreigntype,y,x," and gridid='"+req.session.user.gridid+"'");
        }else{
            sqlstr1=sqlstr1.format(y,foreigntype,y,x,"");
        }
        command.executesql(sqlstr1,(result1)=>{
            res.json({resultStatus:result.resultStatus,data:{data1:result.data,data2:result1.data}});
        });
    });
});
//流动人口按照小区分类统计
router.get('/ldrktjBybuilding', function (req, res, next) {
    let sqlstr1=`select  districtname name  ,sum(num) total from  building a,(
    select  b.buildingid,count(*) num
              from temporary a, house b
             where a.houseid = b.id  group by  buildingid) b where a.id=b.buildingid group by districtname  `;
             command.executesql(sqlstr1,(result)=>{
                res.json(result);
            });
    });

//流动留守儿童

router.get('/ldlschildren', function (req, res, next) {
    let sqlstr="select t.id,  DISTRICTNAME  xqm,name ldh,t.DISTRICTNAME||t.name||':'||t.xm name,t.x,t.y,t.unitnum,t.floornum,t.roomnum,t.zjhm,t.xm,t.ldls ,'' color from  permanent_ldlsChildren_view t";
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
    });


// //人民防线
// router.get('/rmfx', function (req, res, next) {
//     let sqlstr="select id,DISTRICTNAME||name name,wkt ,rmfx color,rmfx  marktype,type,unitnum,floornum,roomnum ,gridid from buiding_view where rmfx is not null  union  all  select id, gridman name, wkt, color,  '7' rmfx,'' type, 0 unitnum,0 floornum,0 roomnum ,id  gridid from communitygrid a, population b where a.wgyzjhm = b.zjhm ";
//     command.executesql(sqlstr,(result)=>{
//         res.json(result);
//     });
// });

//人民防线
router.get('/rmfx', function (req, res, next) {
    let sqlstr="select id,DISTRICTNAME||name name,wkt ,rmfx color,rmfx  marktype,type,unitnum,floornum,roomnum ,gridid from buiding_view where rmfx is not null ";
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});


//人民防线
router.get('/fmfxgrid', function (req, res, next) {
    let sqlstr="select id, gridman name ,a.name address, wkt, color ,wgz,wgylxdh,csny,zzmm,whcd ,xb from communitygrid a, population b where a.wgyzjhm = b.zjhm  order by CAST(id AS INTEGER) ";
    command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});



//团员
router.get('/ldls', function (req, res, next) {
    let sqlstr="select id,gridid, DISTRICTNAME  xqm,name ldh,DISTRICTNAME||name name,type,zdry marktype,zljzrq ,wkt ,rooms,unitnum,floornum,roomnum,zdry color,ldls ,qsn from buiding_view where type!='6' and ldls is not null {0}";
    if(req.session.user.gridid){
        sqlstr=sqlstr.format(" and gridid='"+req.session.user.gridid+"'");
    }else{
        sqlstr=sqlstr.format("");
    } 
        command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});

//团委
router.get('/twgrid', function (req, res, next) {
    let sqlstr="select id,gridid, DISTRICTNAME  xqm,name ldh,DISTRICTNAME||name name,type,zdry marktype,zljzrq ,wkt ,rooms,unitnum,floornum,roomnum,zdry color,ldls ,qsn from buiding_view where type!='6' and ldls is not null {0}";
    if(req.session.user.gridid){
        sqlstr=sqlstr.format(" and gridid='"+req.session.user.gridid+"'");
    }else{
        sqlstr=sqlstr.format("");
    } 
        command.executesql(sqlstr,(result)=>{
        res.json(result);
    });
});


module.exports=router;