R 函数

do_query <- function(conn, sql) {
  res = dbSendQuery(conn, sql)
  data = fetch(res)
  dbClearResult(dbListResults(conn)[[1]])
  data
}

##拼sql语言
gen_sql <- function(sku_id, dc_id, date_scale, start_date, end_date) {
  sql = " SELECT a.sku_id,a.dc_id,a.calc_date, round(("
  for (i in seq(1,date_scale)) {
    sql = paste(sql, "a.fs", i, sep = "")
    if (i != date_scale) {
      sql = paste(sql, "+", sep = "")
    }
  }
  sql = paste(sql, "),2) as fs, (SELECT SUM(real_sales) FROM residual_analysis WHERE (TO_DAYS(calc_date)-TO_DAYS(a.calc_date))>=0 AND  (TO_DAYS(calc_date)-TO_DAYS(a.calc_date)) <", date_scale)
  sql = paste(sql, "AND dc_id=a.dc_id AND sku_id=a.sku_id) AS rs FROM residual_analysis a WHERE a.sku_id=", sku_id, " AND a.dc_id=", dc_id, "AND a.fs1!=-1") 
  sql = paste(sql, "AND calc_date >=", start_date, " AND calc_date <= ", end_date, " ORDER BY a.calc_date")
}



## formate date from YYYYMMDD to YYYY-MM-DD to be recognized by time series package xts ro as.Date function 
formatDate <- function(dateNoLine) {
  paste(substr(dateNoLine, 1, 4), substr(dateNoLine, 5, 6), substr(dateNoLine, 7, 8), sep = '-')
}


gen_date_list<- function(start_date, end_date) {
  date_list = c()
  start_date = as.Date(formatDate(start_date))
  end_date = as.Date(formatDate(end_date))
  while (start_date < end_date) {
    date_list = append(date_list, gsub("-", "",start_date))
    start_date = start_date + 1
  }
  return (date_list)
}