def insert(result, base_date, shop_id, item_id):
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
sale_date = base_date
for pred in result:
sale_date = datetime.strptime(sale_date, "%Y-%m-%d")
sale_date = sale_date + timedelta(days=1)
sale_date = sale_date.strftime("%Y-%m-%d")
sql = 'insert into forecast_table values (' + \
str(0) + ',' + \
'\'' + str(base_date) + '\',' + \
'\'' + str(sale_date) + '\',' + \
str(shop_id) + ',' + \
str(item_id) + ',' + \
str(pred[0]) + \
');'
cursor.execute(sql)
db.commit()
cursor.close()
db.close()
def insert(anuual_df, base_date, shop_id, item_id):
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
# base date 변환
temp = datetime.strptime(base_date, "%Y-%m-%d")
temp = date(temp.year, 1, 1)
sale_date = temp.strftime("%Y-%m-%d")
for pred in anuual_df:
sale_date = datetime.strptime(sale_date, "%Y-%m-%d")
sale_date = sale_date + timedelta(days=1)
sale_date = sale_date.strftime("%Y-%m-%d")
season = get_season(sale_date)
sql = 'insert into monthly_forecast_table values (' + \
str(0) + ',' + \
'\'' + str(base_date) + '\',' + \
'\'' + str(sale_date) + '\',' + \
'\'' + str(season) + '\',' + \
str(shop_id) + ',' + \
str(item_id) + ',' + \
str(pred[0]) + \
');'
cursor.execute(sql)
db.commit()
cursor.close()
db.close()
def check_base_date(shop_id, item_id):
df = saleDAO.get_sale_array(shop_id, item_id)
if df is None or len(df) < 14:
return 0
base_date = datetime.strftime(df.iloc[-1:]['saleDate'].values[0], "%Y-%m-%d")
base_date = str(base_date)
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
sql = 'select * from forecast_table where base_date >= \'' + base_date + '\' and shop_id = ' + \
str(shop_id) + ' and item_id = ' + str(item_id) + ' order by base_date desc;'
cursor.execute(sql)
result = cursor.fetchall()
for data in result:
return -1
db.commit()
cursor.close()
db.close()
return 1
def setState(request_id, state):
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
sql = 'update request_table set state = \'' + \
state + '\' where request_id = ' + str(request_id) + ';'
cursor.execute(sql)
db.commit()
cursor.close()
db.close()
def setTrained(request_id, trained):
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
sql = 'update request_table set trained = \'' + \
str(trained) + '\' where request_id = ' + str(request_id) + ';'
cursor.execute(sql)
db.commit()
cursor.close()
db.close()
def get_current_request_id():
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
sql = 'select * from request_table order by request_date asc;'
cursor.execute(sql)
result = cursor.fetchall()
request_id = None
for data in result:
request_id = int(data[0])
db.commit()
cursor.close()
db.close()
return request_id
def get_recently_trained_request_date(shop_id, item_id):
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
sql = 'select * from request_table where trained = 1 and shop_id = ' + \
str(shop_id) + ' and item_id = ' + str(item_id) + ' order by request_date desc;'
cursor.execute(sql)
result = cursor.fetchall()
request_date = None
for data in result:
request_date = str(data[3])
db.commit()
cursor.close()
db.close()
return request_date
def add(shop_id, item_id):
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
now = datetime.now()
request_date = now.strftime('%Y-%m-%d')
sql = 'insert into request_table values (' + \
str(0) + ',' + \
str(shop_id) + ',' + \
str(item_id) + ',' + \
'\'' + request_date + '\',' + \
'\'처리 중\'' + \
', 0' + \
');'
cursor.execute(sql)
db.commit()
cursor.close()
db.close()
return get_current_request_id()
def check_base_date(shop_id, item_id):
df = saleDAO.get_sale_array_asc(shop_id, item_id)
max_sale_date_str = datetime.strftime(df.iloc[-1:]['saleDate'].values[0], "%Y-%m-%d")
max_sale_date_str = str(max_sale_date_str)
if df is None or len(df) < 14:
# 데이터가 14개 이하일 경우
return 0
# ============================================================================
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
sql = 'select * from forecast_table where shop_id = ' + \
str(shop_id) + ' and item_id = ' + str(item_id) + ' order by base_date desc;'
cursor.execute(sql)
result = cursor.fetchall()
if len(result) != 0:
recent_base_date = result[0][1]
recent_base_date = datetime.strftime(recent_base_date, "%Y-%m-%d")
db.commit()
cursor.close()
db.close()
max_sale_date = datetime.strptime(max_sale_date_str, "%Y-%m-%d")
recent_base_date = datetime.strptime(recent_base_date, "%Y-%m-%d")
diff_date = max_sale_date - recent_base_date
if diff_date < timedelta(days=1):
return -2
# ============================================================================
conn = DBConnector()
db = conn.connect()
cursor = db.cursor()
sql = 'select * from forecast_table where base_date >= \'' + max_sale_date_str + '\' and shop_id = ' + \
str(shop_id) + ' and item_id = ' + str(item_id) + ' order by base_date desc;'
cursor.execute(sql)
result = cursor.fetchall()
for data in result:
return -1
db.commit()
cursor.close()
db.close()
return 1