// 存放一些通用方法
|
|
/**
|
* 拼接数组
|
* @param values 待拼接数组
|
* @param separator 分隔符
|
*/
|
let array_connect = function(values, separator) {
|
if (values.length) {
|
let str = ''
|
for (var idx in values) {
|
str += `'${values[idx]}'${separator}`
|
}
|
return str.substring(0, str.length - 1)
|
} else {
|
return ''
|
}
|
}
|
|
/**
|
* 生成where条件
|
* @param options 条件集合，类型为json数组；每一个条件为一个单独的json对象，包括3个key
|
* 即"type"、"name"、"value"；type为字符型，取值及含义为 "1":等于，"2":小于，"3":大于，
|
* "12": 小于等于， "13": 大于等于， "4": between， "5": not between， "6": in ，"7": not in ；
|
* name为字段名； value为值， 当且仅当type为4、 5、 6 ,7时， 以数组形式保存；
|
* "8":模糊查询
|
* value的值如果为字符串，需要用引号括起来
|
* @param operator 条件连接运算符“ AND” 或者“ OR”
|
*/
|
let create_whereclause = function(options, operator) {
|
if (!operator) {
|
operator = 'AND'
|
}
|
if (options && options.length > 0) {
|
let str = ''
|
for (var idx in options) {
|
str += `${operator} ${options[idx]['name']} `
|
switch (options[idx]['type']) {
|
case '1':
|
str += ` = '${options[idx]['value']}' `
|
break
|
case '2':
|
str += ` < '${options[idx]['value']}'' `
|
break
|
case '3':
|
str += ` > '${options[idx]['value']}'' `
|
break
|
case '12':
|
str += ` <= '${options[idx]['value']}'' `
|
break
|
case '13':
|
str += ` >= '${options[idx]['value']}'' `
|
break
|
case '4':
|
str += ` BETWEEN '${options[idx]['value'][0]}' AND '${
|
options[idx]['value'][1]
|
}' `
|
break
|
case '5':
|
str += ` NOT BETWEEN '${options[idx]['value'][0]}' AND '${
|
options[idx]['value'][1]
|
}' `
|
break
|
case '6':
|
str += ` IN (${array_connect(options[idx]['value'], ',')}) `
|
break
|
case '7':
|
str += `NOT IN (${array_connect(options[idx]['value'], ',')}) `
|
break
|
case '8':
|
str += ` LIKE '%${options[idx]['value']}%' `
|
break
|
case '9':
|
str += ` is null `
|
break
|
case '10':
|
str += ` is not null `
|
break
|
}
|
}
|
return ` WHERE ${str.substring(operator.length)} ` //去掉第一个元素前的条件连接符
|
} else {
|
return ' '
|
}
|
}
|
|
/**
|
* 生成order by语句
|
* @param {JSON} orderby "{"fieldname1":"ASC","fieldname2":"DESC",...}"
|
*/
|
let create_orderby = function(orderby) {
|
if (orderby) {
|
let rst = ''
|
let connector = ','
|
for (let key in orderby) {
|
rst = `${connector}${key} ${orderby[key]}`
|
}
|
return ` ORDER BY ${rst.substring(connector.length)} `
|
} else {
|
return ' '
|
}
|
}
|
|
let create_limit = function(rows_per_page, current_page) {
|
if (rows_per_page && current_page) {
|
return ` LIMIT ${rows_per_page * (current_page - 1)},${rows_per_page} `
|
} else {
|
return ' '
|
}
|
}
|
|
/**
|
* 生成select语句
|
* @param {String} table
|
* @param {Array<String>} fields
|
* @param {Array<JSON>} options where条件
|
* @param {String} operator 条件连接符："AND"或者"OR"
|
* @param {JSON} orderby 排序 {"fieldname1":"ASC","fieldname2":"DESC",...}
|
* @param {Number} rows_per_page 每页行数
|
* @param {Number} current_page 当前页码
|
*/
|
let create_sql_select = function(
|
table,
|
fields,
|
options,
|
operator,
|
orderby,
|
rows_per_page,
|
current_page
|
) {
|
return `
|
SELECT ${fields ? array_connect(fields, ',') : '*'}
|
FROM ${table}
|
${create_whereclause(options, operator)}
|
${create_orderby(orderby)}
|
${create_limit(rows_per_page, current_page)}`
|
}
|
|
/**
|
* 生成select语句 [sql_count、sql]
|
* @param {Object} obj select_ from_ where_ order_ server_paging pageindex_ pagesize_
|
*/
|
let create_sql = function(obj,pageindex_,pagesize_) {
|
let str_select_ = [];
|
let str_select_count = [];
|
str_select_.push(obj.select_ + obj.from_ + obj.where_ + obj.order_);
|
str_select_count.push('select count(1) as counter'+obj.from_ + obj.where_ + obj.order_);
|
if (obj.server_paging == '1') {
|
let pageindex = Number.isNaN(Number(pageindex_)) ? Number(pageindex_) : 1;
|
let pagesize = Number.isNaN(Number(pagesize_)) ? Number(pagesize_) : 10;
|
str_select_.push(' limit ').push((pageindex-1)*pagesize).push(', ').push(pagesize);
|
}
|
return [str_select_count.join(''),str_select_.join('')]
|
}
|
|
/**
|
* 生成select语句 [sql_count、sql]
|
* @param {Object} obj select_ from_ where_ order_ server_paging pageindex_ pagesize_
|
*/
|
let create_sync_sql = function(obj) {
|
let str_select_ = [];
|
let str_sync = [];
|
str_select_.push(obj.select_ + obj.from_ + obj.where_ + obj.order_);
|
str_sync.push('replace into ');
|
str_sync.push(obj.table_name);
|
return [str_select_.join(''),str_sync.join('')]
|
}
|
|
let date_format = function(date_str, format) {
|
// = 'YY-MM-DD hh:mm:ss'
|
let date = new Date(date_str)
|
|
let year = date.getFullYear(),
|
month = date.getMonth() + 1, //月份是从0开始的
|
day = date.getDate(),
|
hour = date.getHours(),
|
min = date.getMinutes(),
|
sec = date.getSeconds()
|
let preArr = Array.apply(null, Array(10)).map(function(elem, index) {
|
return '0' + index
|
}) ////开个长度为10的数组 格式为 00 01 02 03
|
|
let newTime = format
|
.replace(/YY/g, year)
|
.replace(/MM/g, preArr[month] || month)
|
.replace(/DD/g, preArr[day] || day)
|
.replace(/hh/g, preArr[hour] || hour)
|
.replace(/mm/g, preArr[min] || min)
|
.replace(/ss/g, preArr[sec] || sec)
|
|
return newTime
|
}
|
|
/**
|
* 当月第一天和最后一天
|
* @param {*} date
|
*/
|
let month_first_end = function(date) {
|
let M = typeof date == 'undefined' ? new Date() : date.getMonth()
|
let Y = typeof date == 'undefined' ? new Date() : date.getFullYear()
|
let day = new Date(Y, M + 1, 0).getDate()
|
let enddate = new Date(Y, M, day)
|
let result = {
|
start: date_format(new Date(Y, M, 1), 'YY-MM-DD'),
|
end: date_format(enddate, 'YY-MM-DD')
|
}
|
|
return result
|
}
|
|
module.exports = {
|
create_sql,
|
create_sql_select,
|
create_sync_sql,
|
array_connect,
|
create_whereclause,
|
create_orderby,
|
create_limit,
|
date_format,
|
month_first_end,
|
}
|
