工具: natis

require('mstr');
var dateutil = require('mdate');
var strs = "abcdefghijklmnopqrstuvwxyz0123456789abc";
var allTags = ["eq", "gt", "gte", "lt", "lte", "neq", "like", "in"]
module.exports = function(template) {

    // {{columns | batch:'username, age, name'}}
    template.helper('batch', function(data,columns){
        if(data == undefined || !Array.isArray(data)){
            return ''
        }
        var coldata = []
        //解析columns
        var cols = columns.split(',')
        for(var i in cols){
            //进一步解析
            var t0 = cols[i].split(':')
            var item = {
                'column':t0[0].trim()
            }
            if(t0.length > 1){
                let t1 = t0[1]
                item.defaultVal = t1
            }
            coldata.push(item)
        }

        //组装sql
        var result = ""
        for(var i in data){
            var temp = "("
            var item = data[i]
            for(var j in coldata){
                var col = coldata[j]
                if(item[col.column] != undefined){
                    temp += getStr(item[col.column])
                }
                else{
                    if(col['defaultVal'] != undefined){
                        temp += getStr(col['defaultVal'])
                    }
                    else if(col.column == 'now'){
                        temp += getStr(dateutil.format())
                    }
                    else{
                        temp += getStr('')
                    }
                }
                if(j < coldata.length - 1){
                    temp += ","
                }
            }
            temp += ')'
            if(i < data.length - 1){
                temp += ","
            }
            result += temp
        }
        return result

    })

    //['字段值']  {{ val | q, defaultVal}}
    template.helper('q', function(val, defaultVal) {
        if(val == undefined){
            if(defaultVal == undefined){
                val = ''
            }
            else{
                val = defaultVal
            }
        }
        return getStr(val)
    });

    //[字段名 = '字段值'] {{ val | eq:'col'}}
    template.helper('eq', function(val, col, defaultVal, invalidValues) {
        if(val == undefined){
            if(defaultVal == undefined){
                return ''
            }
            val = defaultVal
        }
        if(!isValid(val, invalidValues)){
            return ''
        }
        return getSql(col, '=', val)
    });


    //[字段名 = '字段值',] {{ val | ceq:'col'}}
    template.helper('ceq', function(val, col, defaultVal, invalidValues) {
        if(val == undefined){
            if(defaultVal == undefined){
                return ''
            }
            val = defaultVal
        }
        if(!isValid(val, invalidValues)){
            return ''
        }
        return getSql(col, '=', val) + ","
    });

    //[,] {{data | m:index}}
    template.helper('m', function(data, index) {
        if (data.length > (index + 1)) {
            return ',';
        }
        return '';
    });

    //['当前时间'] {{now | d}}
    template.helper('d', function(val, format) {
        var result = dateutil.format(val, format);
        return "'" + (result || "") + "'";
    });

    //[字段名 = '当前日期'] {{now | ed:'col'}}
    template.helper('ed', function(val, col) {
        return " " + col + " = '" + dateutil.format() + "' ";
    });

    //[ and 字段名 = '字段值']    {{val | and:col,'eq'}}
    //[ and 字段名 > '字段值'] {{val | and:col,'gt'}}
    //[ and 字段名 >= '字段值'] {{val | and:col,'gte'}}
    //[ and 字段名 < '字段值'] {{val | and:col,'lt'}}
    //[ and 字段名 <= '字段值'] {{val | and:col,'lte'}}
    //[ and 字段名 != '字段值'] {{val | and:col,'neq'}}
    //[ and 字段名 like '%字段值%'] {{val | and:col,'like'}}
    //[ and 字段名 in (1,2)] {{val | and:col,'in'}}
    //[ and (字段1 like '%值%' or 字段2 like '%值%' ...)] {{val | and:'col1,col2...','like'}}

    template.helper('and', function(val, col, tag, defaultVal, invalidValues) {
        if(allTags.indexOf(tag) < 0){
            invalidValues = tag
            tag = "eq"
        }
        if(val == 'now'){
            val = dateutil.format('now', 'yyyy-MM-dd HH:mm:ss');
        }
        if(val == undefined){
            //使用defaultVal
            if(defaultVal == undefined){
                return ''
            }
            val = defaultVal
        }

        if(!isValid(val, invalidValues)){
            return ''
        }

        tag = tag || 'eq';

        var tags = {
            eq: '=',
            neq: '!=',
            lt: '<',
            lte: '<=',
            gt: '>',
            gte: '>=',
            like: 'like',
            'in': 'in'
        }
        if (tag == 'like') {
            val = "%" + escape(val) + "%";
        }else if (tag == 'llike') {
            val = "%" + escape(val);
        } else if (tag == 'in') {
            val = " (" + val + ") ";
        }
        var realTag = tags[tag];
        if (realTag) {
            //同一个值被多个字段引用
            var cols = col.split(",");
            if (cols.length == 1) {
                return " and " + getSql(col, realTag, val);
            } else {
                var sqls = [];
                for (var i = 0; i < cols.length; i++) {
                    sqls.push(getSql(cols[i], realTag, val));
                }
                return " and (" + sqls.join(" or ") + " )";
            }
        } else {
            return '';
        }
    });

    // [ order by id desc ] {{ val | orderby}}
    template.helper('orderBy', function(val, defaultVal) {
        if (val == null || val == '') {
            return ' order by ' + (defaultVal || 'id asc') + ' ';
        }
        return ' order by ' + val + ' ';
    });
    // [limit offset,pageSize] {{offset | limit:pageSize}}
    template.helper('limit', function(offset, pageSize) {
        if(offset == undefined || pageSize == undefined){
            return ''
        }
        if (pageSize > 0) {
            return ' limit ' + offset + ',' + pageSize;
        }
        return '';
    });

    function isValid(val, invalidValues){
        //如果val在invalidValues中，则不会组装sql
        if(invalidValues != undefined){
            if(Array.isArray(invalidValues)){
                for(var i in invalidValues){
                    if(val === invalidValues[i]){
                        return false
                    }
                }
            }
            else if(invalidValues === val){
                return false
            }
        }
        return true
    }
    function getSql(col, tag, val) {
        return " " + col + " " + tag + " " + getStr(val);
    }

    function getStr(s){
        if(typeof s == "string"){
            return "'" + escape(s) + "'"
        }
        return s
    }

    function escape(val) {
        val = val + '';
        return val.replace(/\\/g, "\\\\").replace(/'/g, "\\'");
    }
}