def lastday_margin(cmid, store_id, source_id, item_cat1_ignores, days_delay):
sql = "select sum(total_sale-total_cost) from public.cost_%s where date between '2018-05-01' and getdate()::date-%s " \
"and cmid=%s and foreign_store_id=%s and foreign_category_lv1 not in %s" % (source_id, days_delay, cmid,
store_id, item_cat1_ignores)
data_info = sql_redshift(sql)
return data_info
def lastday_margin(cmid, showcode, source_id, item_cat1_ignores, days_delay):
# 将showcode传递给sql_chain_store方法进行处理，得到foreign_store_id
store_id = sql_chain_store(cmid, showcode)
sql = "select sum(total_sale-total_cost) from public.cost_%s where date=getdate()::date-%s and cmid=%s and foreign_store_id=%s" \
"and foreign_category_lv1 not in %s" % (source_id, days_delay, cmid, store_id, item_cat1_ignores)
return sql_redshift(sql)
def store_inventory_turn_over_rate(source_id, cmid, store_id, days_delay, item_cat1_ignores):
sql = '''
SELECT t1.amount/t2.cost*2 FROM (SELECT SUM(amount) amount FROM inventory_%s WHERE date=getdate()::date
AND cmid=%s and foreign_store_id='%s') t1,( SELECT SUM(total_cost) cost FROM cost_%s WHERE date between
'2018-05-01' and getdate()::date-%s AND cmid=%s and foreign_store_id='%s' and foreign_category_lv1
not in %s) t2
''' % (source_id, cmid, store_id, source_id, days_delay, cmid, store_id, item_cat1_ignores)
data_info = sql_redshift(sql)
return data_info
def ep_month_to_date_sale(cmid, source_id, item_cat1_ignores, days_delay):
cmid = cmid
source_id = source_id
item_cat1_ignores = item_cat1_ignores
days_delay = days_delay
sql = "select sum(total_sale) from public.cost_%s where date BETWEEN '2018-05-01' and getdate()::date-%s and cmid=%s and foreign_category_lv1" \
" not in %s" % (source_id, days_delay, cmid, item_cat1_ignores)
data_info = sql_redshift(sql)
return data_info
def lastday_sale(cmid, source_id, item_cat1_ignores, days_delay):
sql = "select sum(total_sale) from public.cost_%s where date=getdate()::date-%s and cmid=%s and foreign_category_lv1" \
" not in %s" % (source_id, days_delay, cmid, item_cat1_ignores)
data_info = sql_redshift(sql)
return data_info