var express = require('express');
var router = express.Router();
var command=require('../util/database');
var path = require('path');
require('../util/string');
var resultStatus=require('../util/model').resultStatus;
var async = require('async');
//网格查询
router.get('/gridquery/:type', function (req, res, next) {
    let type=req.params.type;
    let shpPath=path.join(__dirname,"../shp/"+type);
    command.executesql("select * from communitygrid", (resultx) => {        
        var shapefile = require("shapefile");
        shapefile.read(shpPath+".shp",shpPath+".dbf")
            .then(source =>{
                    res.json({resultStatus:resultx.resultStatus,data:{grid:resultx.data,shp:source.features}});        
                })
            .catch(error => {
                require("../util/log").logger.info(error);
                res.json({resultStatus:resultx.resultStatus,data:{grid:resultx.data}});
            });
        });
});
//楼栋内所有户
// buildingid: "626"
// dy: null
// floororder: 1
// id: "XDHY87_1_104"
// mj: null
// name: "104"
// roomorder: 2
// type: null
// unitorder: 2
// zdry: null
// zljzrq: null
router.get('/queryfwjg/:type/:id', function (req, res, next) {
    let x=req.params.id;
    let type=req.params.type;
    let sqlstr="select * from house_view t where t.BUILDINGID='{0}'";
    if(type=="1"){
         sqlstr="select * from house_company t where t.BUILDINGID='{0}'";
    }
    command.executesql(sqlstr.format(x),(result)=>{
        res.json(result);
    });
});
//常住人口
router.get('/queryczrk/:id', function (req, res, next) {
    let x=req.params.id;
    let sqlstr=`select d.*,
    c.yhzgx,c.bz,  substr(c.gxsj,0,10) lrsj,
    (select listagg(a2.keyid || ':' || a2.extenddata, ',') within
          group(
          order by a2.keyid)
           from keypersonal a1, keypersonal_type a2
          where a1.type = a2.keyid
            and a1. zjhm = d.zjhm
          group by d.zjhm) zdryextenddata,
    (select count(1) dysl from partymember where zjhm = d.zjhm) dysl,
    (select count(1) qsnsl from QSN_VIEW where zjhm = d.zjhm) qsnsl,
    (select type ldlssl from local_zdry_ldlschildren   where zjhm = d.zjhm) ldlssl,
    (select keyid from local_rmfx   where zjhm = d.zjhm) rmfx 
   from house b, permanent c, population d
   where b.id = c.houseid
   and c.zjhm = d.zjhm
   and b.id = '{0}'  order  by to_date(c.gxsj,'yyyy-mm-dd hh24:mi:ss') desc nulls  last`;
    command.executesql(sqlstr.format(x),(result)=>{
        res.json(result);
    });
});
//暂住人口
router.get('/queryldrk/:id', function (req, res, next) {
    let x=req.params.id;
    let sqlstr=`select d.*,
    c.zljzrq,  c.zlksrq,c.bz, substr(c.gxsj,0,10) lrsj,
    (select listagg(a2.keyid || ':' || a2.extenddata, ',') within
    group(
    order by a2.keyid)
     from keypersonal a1, keypersonal_type a2
    where a1.type = a2.keyid
      and a1. zjhm = d.zjhm
    group by d.zjhm) zdryextenddata,
    (select count(1) dysl from partymember where zjhm = d.zjhm) dysl,    
    (select type ldlssl from local_zdry_ldlschildren   where zjhm = d.zjhm) ldlssl ,
    (select keyid from local_rmfx   where zjhm = d.zjhm) rmfx 
   from house b, TEMPORARY c, population d
   where b.id = c.houseid
   and c.zjhm = d.zjhm
   and b.id = '{0}' order  by to_date(c.gxsj,'yyyy-mm-dd hh24:mi:ss') desc nulls  last`;
    command.executesql(sqlstr.format(x),(result)=>{
        res.json(result);
    });
});
//企业
router.get('/querybusiness/:id', function (req, res, next) {
    let x=req.params.id;
    let sqlstr=`select c.*, (select name from ORGANIZATION_TYPE where id=c.jglx and rownum<=1) jglxmc, 
    (select name from business_type where id=c.djzclx and rownum<=1) djzclxmc, 
    (select name from operation_type where id=c.yyzt and rownum<=1) yyztmc
    from com_house t, HOUSE_COMPANY b, enterprise c
   where t.houseid = b.id
     and c.id = t.id and b.id='{0}'`;
    command.executesql(sqlstr.format(x),(result)=>{
        sqlstr=`select c.*
    from com_house t, HOUSE_COMPANY b, SELFEMPLOYED c
   where t.houseid = b.id
     and c.id = t.id  and b.id='{0}'`;
     command.executesql(sqlstr.format(x),(result1)=>{
        res.json({resultStatus:result.resultStatus,data:{data1:result.data,data2:result1.data}});
     });
    });
});
//分页人口
router.post('/queryrkpage', function (req, res, next) {
    let pagerows=req.body.pagerows;
    let index=req.body.index;
    let condition=req.body.condition;
    let sqlstr1="select t.*,rownum rn from population t where 1=1";
    let sqlstr_condtion="";
    if(condition&&condition.name){
        sqlstr_condtion+=" and t.name like  '%"+condition.name+"%' ";
    }
    if(condition&&condition.xb){
        sqlstr_condtion+=" and t.xb like  '%"+condition.xb+"%' ";
    }
    if(condition&&condition.zjhm){
        sqlstr_condtion+=" and t.zjhm like  '%"+condition.zjhm+"%' ";
    }
    if(req.session.user.gridid){
        sqlstr_condtion+=" and   exists(select 1 from pupulation_building_view a1,address_detail_view a2 where a1.houseid=a2.id and a1.zjhm=t.zjhm and a2.gridid='"+req.session.user.gridid+"')";
    }else{
        //未能关联房屋
        if(condition&&condition.unrelation){
            sqlstr_condtion+=" and  not exists(select 1 from pupulation_building_view aa where aa.zjhm=t.zjhm) ";
        }
    }
    //暂住人口、租客到期
    if (condition.temporary  || condition.expire) {
        sqlstr_condtion += " and  exists(select 1 from temporary aa where aa.zjhm=t.zjhm ";
        //condition.temporary && (sqlstr_condtion += " and aa.zljzrq is not null");
        condition.expire && (sqlstr_condtion += " and checktime(aa.zljzrq, 0)='1'");
        sqlstr_condtion +=")";
    }//党员
    if(condition&&condition.party){
        sqlstr_condtion+=" and  exists(select 1 from partymember bb where bb.zjhm=t.zjhm) ";
    }//重点人员
    if(condition&&condition.zdry){
        sqlstr_condtion+=" and  exists(select 1 from keypersonal cc where cc.zjhm=t.zjhm and instr(',"+condition.zdry+",',concat(concat(',',cc.type),','))>0 ) ";
    }
    let sqlstr=`select a1.*,
    (case when tag.zjhm is not null then '1' else null end)tag, 
    zdry.zdry 
    from ( {2} and rownum <={1}) a1, 
    (select zjhm from partymember) tag, 
    (select zjhm, wm_concat(type) zdry from keypersonal group by zjhm) zdry
    where  a1.zjhm = tag.zjhm(+)
    and a1.zjhm = zdry.zjhm(+) 
    and rn >{0}`;
    sqlstr=sqlstr.format((index-1)*pagerows,(index+0)*pagerows,sqlstr1+sqlstr_condtion);
    command.executesql(sqlstr,(result)=>{
        if(req.body.first){
            command.executesql("select count(1) sl from population t where 1=1 "+sqlstr_condtion,(result1)=>{
                res.json({
                    resultStatus: result.resultStatus,
                    data: {
                        data: result.data,
                        number: result1.data[0].sl
                    }
                });
            });
        }else{
            res.json(result);
        }
    });
});
//党员、所属楼栋查询详情
router.post('/querydyxq', function (req, res, next) {
    let condition = req.body;
    command.executesqlwithoutstatus("select * from partymember t where zjhm='" + condition.zjhm + "'", (result1) => {
        command.executesql("select t .*,a.zljzrq from address_detail_view  t,pupulation_building_view a  where a.houseid=t.id and a.zjhm='" + condition.zjhm + "'", (result) => {
            res.json({
                resultStatus: result.resultStatus,
                data: {
                    data1: result1,
                    data2: result.data
                }
            });
        });
    });
});
//查询重点人员详情
router.post('/queryzdryxq', function (req, res, next) {
    let condition = req.body;

    var data=[];
    async.each(condition.type, (item, callback) => {
        command.executefunwithoutstatus("pkg_keypersonal.fuc_querykeypersonal", {
            zjhm: condition.zjhm,
            type: item
        }, true, (result) => {
            if(result&&result.length>=0)
            data=data.concat({key:item,data:result[0]});
            callback();
        });
      }, function (err) {
        if (err) {
            res.json({ resultStatus: resultStatus.error(err) });
        } else {
            res.json({ resultStatus: resultStatus.success, data: data });
        }
      });
});
//人口模糊查询
router.post('/queryrk', function (req, res, next) {
    let condition=req.body.condition;
    let sqlstr_condtion="";
    if(condition&&condition.name){
        sqlstr_condtion+=" and t.name like  '%"+condition.name+"%' ";
    }
    if(condition&&condition.xb){
        sqlstr_condtion+=" and t.xb like  '%"+condition.xb+"%' ";
    }
    if(condition&&condition.zjhm){
        sqlstr_condtion+=" and t.zjhm like  '%"+condition.zjhm+"%' ";
    }
    command.executesql("select * from population t where 1=1 "+sqlstr_condtion,(result)=>{
        res.json(result);
    });
});
//分页楼栋
router.post('/querypagebuilding', function (req, res, next) {
    let pagerows=req.body.pagerows;
    let index=req.body.index;
    let condition=req.body.condition;
    let sqlstr1="select t.*,rownum rn,(select name from communitygrid aa where aa.id=t.gridid and rownum<=1) gridname from building t where t.type!='6' ";
    let sqlstr_condtion="";
    if(condition&&condition.name){
        sqlstr_condtion+=" and t.name like  '%"+condition.name+"%' ";
    }
    if(condition&&condition.districtname){
        sqlstr_condtion+=" and t.districtname like  '%"+condition.districtname+"%' ";
    }
    if(condition&&condition.facade){
        sqlstr_condtion+=" and exists(select 1 from house_company where buildingid=t.id ) ";
    }    
    if(req.session.user.gridid){
        sqlstr_condtion+=" and t.gridid ='"+req.session.user.gridid+"'";
    }
    let sqlstr="select a1.* from ( {2} and rownum <={1}) a1 where rn >{0}";
    command.executesql(sqlstr.format((index-1)*pagerows,(index+0)*pagerows,sqlstr1+sqlstr_condtion),(result)=>{
        if(req.body.first){
            command.executesql("select count(1) sl from building t where t.type!='6'  "+sqlstr_condtion,(result1)=>{
                res.json({
                    resultStatus: result.resultStatus,
                    data: {
                        data: result.data,
                        number: result1.data[0].sl
                    }
                });
            });
        }else{
            res.json(result);
        }
    });
});
//查询照片
router.get('/queryphotos/:id/:folder', function (req, res, next) {
    command.executesql("select id,folder,path, domain||FOLDER||'/'||PATH src,name alt from photos where areaid='" + req.params.id + "'  and folder='"+req.params.folder+"'", (result) => {
        res.json(result);
    });
});

//年龄段统计
router.get('/year', function (req, res, next) {
    // command.executefun("pkg_self.fun_queryyear", {

    // }, true, (result) => {
    //     res.json(result);
    // });

    command.executesql( `select sum(case
    when nl < 15 then
     1
    else
     0
  end) "0-14",
  sum(case
    when nl > 14 and  nl < 29 then
     1
    else
     0
  end) "15-28",
  sum(case
    when nl > 28 and  nl < 36 then
     1
    else
     0
  end) "29-35",
sum(case
    when nl <= 59 and nl >= 36 then
     1
    else
     0
  end) "36-59",
sum(case
    when nl <= 69 and nl >= 60 then
     1
    else
     0
  end) "60-69",
sum(case
    when nl <= 79 and nl >= 70 then
     1
    else
     0
  end) "70-79",
sum(case
    when nl <= 80 and nl >= 89 then
     1
    else
     0
  end) "80-89",

sum(case
    when nl >= 90 then
     1
    else
     0
  end) "90"
from (select to_char(sysdate, 'yyyy') - substr(csny, 0, 4) nl
 from population t
where csny is not null)`, (result) => {
     res.json(result);
  });
});
  //////////////////////////////场所

//场所基础信息查询
router.get('/areabasequery/: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('/areadetailquery/:id/:extenddata', function (req, res, next) {
    let id = req.params.id;
    let extenddata = req.params.extenddata;
    let sqlstr = "select * from " + extenddata + " where id='" + id + "'";
    command.executesql(sqlstr, (result) => {
      sqlstr = " select lower(column_name) key, nvl(comments, lower(column_name)) comments from user_col_comments where table_name = upper('" + extenddata + "')";
      command.executesql(sqlstr, (result1) => {
        if(result.data&&result.data.length>0){
          result1.data.forEach(v => {
            for (let item in result.data[0]) {
              if (item == v.key) {
                v.data = result.data[0][item];
              }
            }
            return v;
          });
        }
        res.json(result1);
      });
    });
  });
  
  //场所关联监控查询
  router.get('/queryassociateedmonitor/:id', function (req, res, next) {
    let areaid = req.params.id;
    let sqlstr = "select a.id,a.name from monitor a,area_monitor b where a.id=b.monitorid  and areaid='"+areaid+"'";
    command.executesql(sqlstr, (result) => {
      res.json(result);
    });
  });

  //场所类型查询
router.get('/areatypequery', function (req, res, next) {
    let sqlstr = " select * from area_type";
    command.executesql(sqlstr, (result) => {
      res.json(result);
    });
  });

  //监控查询
  router.get('/querymonitor', function (req, res, next) {
    let sqlstr = "select id ,name from monitor ";
    command.executesql(sqlstr, (result) => {
      res.json(result);
    });
  });

/////////////////////////////////////门面房
//个体户分页查询
router.post('/querygthpage', function (req, res, next) {
    let pagerows=req.body.pagerows;
    let index=req.body.index;
    let condition=req.body.condition;
    let sqlstr1="select t.*,rownum rn from selfemployed t where 1=1";
    let sqlstr_condtion="";
    if(condition&&condition.name){
        sqlstr_condtion+=" and t.gtjyhhzxm like  '%"+condition.name+"%' ";
    }
   
    let sqlstr=`select a1.*
    from ( {2} and rownum <={1}) a1
    where rn >{0}`;
    sqlstr=sqlstr.format((index-1)*pagerows,(index+0)*pagerows,sqlstr1+sqlstr_condtion);
    command.executesql(sqlstr,(result)=>{
        if(req.body.first){
            command.executesql("select count(1) sl from selfemployed t where 1=1 "+sqlstr_condtion,(result1)=>{
                res.json({
                    resultStatus: result.resultStatus,
                    data: {
                        data: result.data,
                        number: result1.data[0].sl
                    }
                });
            });
        }else{
            res.json(result);
        }
    });
});
//企业分页查询
router.post('/queryqypage', function (req, res, next) {
    let pagerows=req.body.pagerows;
    let index=req.body.index;
    let condition=req.body.condition;
    let sqlstr1=`select t.*,
    rownum rn from enterprise t where 1=1`;
    let sqlstr_condtion="";
    if(condition&&condition.name){
        sqlstr_condtion+=" and t.dwxxmc like  '%"+condition.name+"%' ";
    }
    if(condition&&condition.djzclx){
        sqlstr_condtion+=" and t.djzclx like  '%"+condition.djzclx+"%' ";
    }
    if(condition&&condition.jglx){
        sqlstr_condtion+=" and t.jglx like  '%"+condition.jglx+"%' ";
    }
    let sqlstr=`select a1.*
    from ( {2} and rownum <={1}) a1
    where rn >{0}`;
    sqlstr=sqlstr.format((index-1)*pagerows,(index+0)*pagerows,sqlstr1+sqlstr_condtion);
    command.executesql(sqlstr,(result)=>{
        if(req.body.first){
            command.executesql("select count(1) sl from enterprise t where 1=1 "+sqlstr_condtion,(result1)=>{
                res.json({
                    resultStatus: result.resultStatus,
                    data: {
                        data: result.data,
                        number: result1.data[0].sl
                    }
                });
            });
        }else{
            res.json(result);
        }
    });
});

//企业todo
router.get('/queryqydata/:id', function (req, res, next) {
    let x=req.params.id;
    let sqlstr=`select d.*
   from house_company b, com_house c, enterprise d
   where b.id = c.houseid
   and c.id = d.id
   and b.id = '{0}'`;
    command.executesql(sqlstr.format(x),(result)=>{
        res.json(result);
    });
});
//个体户todo
router.get('/querygthdata/:id', function (req, res, next) {
    let x=req.params.id;
    let sqlstr=`select d.*
   from house_company b, com_house c, selfemployed d
   where b.id = c.houseid
   and c.id = d.id
   and b.id = '{0}'`;
    command.executesql(sqlstr.format(x),(result)=>{
        res.json(result);
    });
});

//企业地址
router.post('/queryfacadeaddress', function (req, res, next) {
    let condition = req.body;
    command.executesql("select t.id,full_address from address_detail_view t,com_house a where a.houseid=t.id and a.id='" + condition.id + "'", (result) => {
        res.json(result);
    });
});

//模糊查询
router.post('/querygthmh', function (req, res, next) {
    let condition=req.body.condition;
    let sqlstr_condtion="";
    if(condition&&condition.name){
        sqlstr_condtion+=" and t.gtjyhmc like  '%"+condition.name+"%' ";
    }
    command.executesql("select t.* from selfemployed t where 1=1 "+sqlstr_condtion,(result)=>{
        res.json(result);
    });
});
router.post('/queryqymh', function (req, res, next) {
    let condition=req.body.condition;
    let sqlstr_condtion="";
    if(condition&&condition.name){
        sqlstr_condtion+=" and t.dwxxmc like  '%"+condition.name+"%' ";
    }
    command.executesql("select t.* from enterprise t where 1=1 "+sqlstr_condtion,(result)=>{
        res.json(result);
    });
});
  //运营状态、机构类型、登记注册类型、党组列表
  router.get('/typesquery', function (req, res, next) {
    command.executesqlwithoutstatus("select * from business_type", (result) => {
        command.executesqlwithoutstatus("select * from operation_type", (result1) => {
            command.executesqlwithoutstatus("select * from organization_type", (result2) => {
                command.executesqlwithoutstatus("select * from partygroup", (result3) => {
                    res.json({
                        resultStatus: resultStatus.success,
                        data: {
                            yyzts: result1,
                            djzclxs: result,
                            jglxs: result2,
                            dzxs:result3,
                        }
                    });
              });
            });
          });
    });
  });

  //#region 社区管理
  //模块查询
router.get('/modulequery', function (req, res, next) {
    command.executesql("select * from module", (resultx) => {
        res.json(resultx);
    });
});
router.get('/moduledataquery/:id', function (req, res, next) {
    command.executesql("select content from module where id='"+req.params.id+"'", (resultx) => {
        res.json(resultx);
    });
    // command.executesql("select * from local_tdgl t", (resultx) => {
    //     res.json(resultx);
    // });
});
  //#endregion

//匹配数据库视频列表 获取channel
router.get('/monitorquery/:id', function (req, res, next) {
    command.executesql("select * from monitor where ip='" + req.params.id + "'", (resultx) => {
        res.json(resultx);
    });
});

// //网格查询
router.get('/gridDetailquery/:gridid', function (req, res, next) {
    let gridid=req.params.gridid;
    command.executesql("select a.id, gridman name, wkt, wgz, zjhm, xb, csny, mz, whcd, b.lxdh  from communitygridman a, population b where a.wgyzjhm = b.zjhm and a.id="+gridid, (resultx) => {       
        res.json(resultx);       
    });
});

// //查询青少年团员人数
router.get('/qsn', function (req, res, next) {
    command.executesql("select sum(case  when nl < 36 then 1 else  0  end) \"35\", sum(case when nl < 29 and nl > 13 then  1 else  0 end) \"14-28\", 85 \"团员\" from (select to_char(sysdate, 'yyyy') - substr(csny, 0, 4) nl from population where csny is not null)", (resultx) => {    
        res.json(resultx);       
    });
});

module.exports = router;
