var express = require('express');
var router = express.Router();
var command = require('../util/database');
var nodeExcel = require('excel-export');
var resultStatus = require('../util/model').resultStatus;

router.use(function (req, res, next) {
    if (req.session.user.manager != "1") {
        res.json({
            resultStatus: resultStatus.noAuthority
        });
    } else {
        require("../util/log").logger.info(req.session.user.userid + req.originalUrl + "////////begin");
        next();
    }
});

//常住excel导出
router.post('/excel_czrk', function (req, res, next) {
    var obj = JSON.parse(req.body.data);
    var sqlstr = `select v.districtname,v.ldh,v.mph,v.zjhm,v.name,v.lxdh,v.zjlx,v.xb,v.xb,v.mz,
        v.csny,v.hkdjd,v.xjzd,v.hyzk,v.yhzgx,v.zjhmqfrq,v.cym,v.csd,v.jg,v.sg,v.xx,v.zzmm,
        v.whcd,v.sfsw,v.gzdw,v.bz
        from PERMANENT_DETAIL_VIEW v where 1=1 `;
    if(req.session.user.gridid){
        sqlstr+=" and v.gridid='"+req.session.user.gridid+"'";
    }
    command.executesqlwithoutstatus(sqlstr, (result) => {
        exportExcel(res, obj.filename, obj.head, result);
    });
});
//暂住excel导出
router.post('/excel_zzrk', function (req, res, next) {
    var obj = JSON.parse(req.body.data);
    var sqlstr = `select v.districtname,v.ldh,v.mph,v.zjhm,v.name,v.lxdh,v.zjlx,v.xb,v.xb,v.mz,
    v.csny,v.hkdjd,v.xjzd,v.hyzk,v.zlksrq,v.zljzrq,v.zjhmqfrq,v.cym,v.csd,v.jg,v.sg,v.xx,v.zzmm,
    v.whcd,v.sfsw,v.gzdw,v.bz
    from TEMPORARY_DETAIL_VIEW v where 1=1 `;
    if(req.session.user.gridid){
        sqlstr+=" and v.gridid='"+req.session.user.gridid+"'";
    }
    command.executesqlwithoutstatus(sqlstr, (result) => {
        exportExcel(res, obj.filename, obj.head, result);
    });
});
//党员excel导出
router.post('/excel_dy', function (req, res, next) {
    var obj = JSON.parse(req.body.data);
    var sqlstr = "";
    if (obj.type == "1") {
        sqlstr = `select v.districtname,v.ldh,v.mph,v.zjhm,v.name,v.lxdh,v.zjlx,v.xb,v.xb,v.mz,
        v.csny,v.hkdjd,v.xjzd,v.hyzk,v.zlksrq,v.zljzrq,a.rdrq,a.ssdz,a.zz,a.szdzb,
        v.zjhmqfrq,v.cym,v.csd,v.jg,v.sg,v.xx,v.zzmm,v.whcd,v.sfsw,v.gzdw,v.bz
        from TEMPORARY_DETAIL_VIEW v,partymember a where v.zjhm=a.zjhm `;
    } else {
        sqlstr = `select v.districtname,v.ldh,v.mph,v.zjhm,v.name,v.lxdh,v.zjlx,v.xb,v.xb,v.mz,
        v.csny,v.hkdjd,v.xjzd,v.hyzk,v.yhzgx,a.rdrq,a.ssdz,a.zz,a.szdzb,v.zjhmqfrq,v.cym,v.csd,
        v.jg,v.sg,v.xx,v.zzmm,v.whcd,v.sfsw,v.gzdw,v.bz
        from PERMANENT_DETAIL_VIEW v ,partymember a where v.zjhm=a.zjhm `;
    }
    if(req.session.user.gridid){
        sqlstr+=" and v.gridid='"+req.session.user.gridid+"'";
    }
    command.executesqlwithoutstatus(sqlstr, (result) => {
        exportExcel(res, obj.filename, obj.head, result);
    });
});
//重点人员excel导出
router.post('/excel_zdry', function (req, res, next) {
    var obj = JSON.parse(req.body.data);
    var sqlstr = "";
    if (obj.type.rk == "zk") {
        sqlstr = `select v.districtname,v.ldh,v.mph,v.zjhm,v.name,v.lxdh,v.zjlx,v.xb,v.xb,v.mz,
        v.csny,v.hkdjd,v.xjzd,v.hyzk,v.zlksrq,v.zljzrq,v.zjhmqfrq,v.cym,v.csd,v.jg,v.sg,v.xx,v.zzmm,
        v.whcd,v.sfsw,v.gzdw,v.bz
        from TEMPORARY_DETAIL_VIEW v where exists(select 1 from KEYPERSONAL a where a.zjhm=v.zjhm
        and a.type='`
            + obj.type.zdry.key + "') ";
    } else {
        sqlstr = `select v.districtname,v.ldh,v.mph,v.zjhm,v.name,v.lxdh,v.zjlx,v.xb,v.xb,v.mz,
        v.csny,v.hkdjd,v.xjzd,v.hyzk,v.yhzgx,v.zjhmqfrq,v.cym,v.csd,v.jg,v.sg,v.xx,v.zzmm,
        v.whcd,v.sfsw,v.gzdw,v.bz
        from PERMANENT_DETAIL_VIEW v where exists(select 1 from KEYPERSONAL a where a.zjhm=v.zjhm 
        and a.type='`
            + obj.type.zdry.key + "') ";
    }
    if(req.session.user.gridid){
        sqlstr+=" and v.gridid='"+req.session.user.gridid+"'";
    }
    command.executesqlwithoutstatus(sqlstr, (result) => {
        exportExcel(res, obj.filename, obj.head, result);
    });
});

//导出
function exportExcel(res, filename, head, result) {
    var data = new Array();
    result.forEach(element => {
        var item = new Array();
        for (var prop in element) {
            item.push(element[prop]);
        }
        data.push(item);
    });

    var conf = {};
    conf.cols = head.map(function (val, index, array) {
        return { caption: val.title, type: 'string' }
    });
    conf.rows = data;
    var result = nodeExcel.execute(conf);
    res.setHeader('Content-Type', 'application/vnd.openxmlformats');
    var d = new Date();
    var str = d.getFullYear() + "_" + d.getMonth() + 1 + "_" + d.getDate() + ":" + d.getSeconds();
    res.setHeader("Content-Disposition", "attachment; filename=" + encodeURIComponent(filename) + "_" + str + ".xlsx");
    res.end(result, 'binary');
}

module.exports = router;