// 存放一些通用方法

/**
 * 拼接数组
 * @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,pagesize_,pageindex_) {
  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_);
  try {
    if (pagesize_ && Number(pagesize_)>=0) {
      let pageindex = (Number(pageindex_)>=0) ? Number(pageindex_) : 1;
      let pagesize = (Number(pagesize_)>=0) ? Number(pagesize_) : 10;
      str_select_.push(' limit ')
      str_select_.push((pageindex-1)*pagesize)
      str_select_.push(', ')
      str_select_.push(pagesize);
    }
  } catch (error) {
    
  }
  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(obj.insert_ + ' ' + obj.column_ + ' ' + obj.values_ + ' ' + obj.updateDuplicate_);

  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,
}