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