def add(cls, **kwargs):
item_status = '淘汰'
cmid = kwargs.get('cmid')
foreign_item_id = kwargs.get('foreign_item_id')
date = arrow.now().datetime
sql = '''
insert into
replace_goods (foreign_item_id, cmid, item_status,date)
values ('{}', {},'{}','{}')'''.format(foreign_item_id, cmid,
item_status, date)
get_df(SERVERDB_DB_URL, sql)
def get_item_cost_by_query(cls, **kwargs):
cmid = kwargs.get('cmid', 34)
barcodes = kwargs.get('barcodes', [])
is_barcode = kwargs.get('is_barcode', None)
start_at = kwargs.get(
'start_at',
arrow.now().shift(months=-2).ceil('month').date())
end_at = kwargs.get('end_at',
arrow.now().shift(months=-1).ceil('month').date())
condition = 'and cg.barcode in ({})'.format("'{}'".format(
"','".join(barcodes))) if is_barcode else ''
sql = '''
select
c.foreign_item_id,
sum(c.total_quantity) as quantity,
sum(c.total_sale) as sale,
sum(c.total_cost) as cost,
sum(c.total_sale - c.total_cost) as profit
from
cost_{}yyyyyyyyyyyyy c
left join chain_goods cg
on
c.foreign_item_id = cg.foreign_item_id
where
c.date > '{}'
and c.date < '{}'
and cg.cmid = {}
{}
group by c.foreign_item_id;'''.\
format(cmid, start_at, end_at, cmid, condition)
res = get_df(REDSHIFT_DB_URL, sql)
return res
def get_category_cost_by_query(cls, **kwargs):
cmid = kwargs.get('cmid', 34)
start_at = kwargs.get(
'start_at',
arrow.now().shift(months=-2).ceil('month').date())
end_at = kwargs.get('end_at',
arrow.now().shift(months=-1).ceil('month').date())
sql = '''
select
c.{},
sum(c.total_quantity) as total_quantity,
sum(c.total_sale) as total_sale,
sum(c.total_cost) as total_cost,
sum(c.total_sale - c.total_cost) as total_profit
from cost_{}yyyyyyyyyyyyy c
left join
chain_goods cg
on
cg.foreign_item_id = c.foreign_item_id
where
c.date > '{}'
and c.date < '{}'
and cg.cmid = {}
group by c.{};'''.format(LOWEST_LEVEL.get(cmid), cmid,
start_at, end_at, cmid,
LOWEST_LEVEL.get(cmid))
res = get_df(REDSHIFT_DB_URL, sql)
return res
def get_barcode_by_query(cls, **kwargs):
cmid = kwargs.get('cmid', 34)
category = kwargs.get('category')
condition = " and {} = '{}'".format(LOWEST_LEVEL.get(cmid),
category) if category else ''
sql = '''
select
barcode
from
chain_goods
where
cmid = {}
{} limit 1;'''.format(cmid, condition)
barcode_res = get_df(REDSHIFT_DB_URL, sql)
barcode = barcode_res.iloc[0].loc['barcode']
file_path = os.path.abspath(os.path.dirname(os.path.dirname(__file__)))
df = pd.read_excel('{}/file/{}.xlsx'.format(file_path,
'temp_category_search'))
category_lv3_name = df[df['barcode'] == int(
barcode)]['category_lv3_name'].values.tolist()
if category_lv3_name:
barcodes = df[df['category_lv3_name'] ==
category_lv3_name[0]]['barcode'].values.tolist()
barcodes = [str(b) for b in barcodes]
else:
barcodes = []
condition = ' and barcode in ({})'.format("'{}'".format(
"','".join(barcodes)))
sql = '''
select
cmid,
barcode
from chain_goods
where cmid
in ({}, {}) {}
and barcode not in (
select
barcode
from chain_goods where cmid = {} {});'''.format(
43, 58, condition, cmid, condition)
res = get_df(REDSHIFT_DB_URL, sql)
return res
def get_all_storage_area(cls):
sql = '''
select
storage_area
from
item_storage_area
group by storage_area;'''
res = get_df(REDSHIFT_DB_URL, sql)
return res
def get_item_by_query(cls, **kwargs):
storage_area = kwargs.get('storage_area')
sql = '''
select
foreign_item_id,
storage_area
from
item_storage_area
where
storage_area = '{}';'''.format(storage_area)
res = get_df(REDSHIFT_DB_URL, sql)
return res
def get_replace_goods_by_query(cls, **kwargs):
cmid = kwargs.get('cmid', 34)
start_at = arrow.now().shift(months=-1).ceil('month').date()
end_at = arrow.now().shift(days=-1).date()
sql = '''
select
foreign_item_id
from
replace_goods
where date > '{}'
and date < '{}'
and cmid = {}
group by
foreign_item_id;'''.format(start_at, end_at, cmid)
res = get_df(SERVERDB_DB_URL, sql)
return res
def get_categories_by_query(cls, **kwargs):
cmid = kwargs.get('cmid', 34)
sql = '''
select
foreign_category_lv1,
foreign_category_lv1_name,
foreign_category_lv2,
foreign_category_lv2_name,
foreign_category_lv3,
foreign_category_lv3_name,
foreign_category_lv4,
foreign_category_lv4_name
from chain_category
where cmid={};'''.format(cmid)
res = get_df(REDSHIFT_DB_URL, sql)
return res
def get_normal_goods_by_query(cls, **kwargs):
cmid = kwargs.get('cmid', 34)
chain_category = kwargs.get('chain_category', None)
barcodes = kwargs.get('barcodes', [])
is_barcode = kwargs.get('is_barcode', None)
condition1 = " and cg.{} = '{}'".format(LOWEST_LEVEL.get(cmid), chain_category) if chain_category else ''
condition2 = ' and cg.barcode in ({})'.format("'{}'".format("','".join(barcodes))) if is_barcode else ''
sql = '''
select
cg.cmid,
cg.foreign_item_id,
cg.foreign_category_lv3,
cg.foreign_category_lv4,
cg.item_status,
cg.show_code,
cg.item_name,
cg.barcode,
cg.brand_name,
cg.supplier_name,
cg.sale_price
from chain_goods cg
left join
data_warehouse_{}yyyyyyyyyyyyy dw
on dw.foreign_item_id = cg.foreign_item_id
where
cg.cmid = {}
and dw.warehouse_name != ''
and cg.item_status = '{}'
{}
{}
group by
cg.cmid,
cg.foreign_item_id,
cg.foreign_category_lv3,
cg.foreign_category_lv4,
cg.item_status,
cg.show_code,
cg.item_name,
cg.barcode,
cg.brand_name,
cg.supplier_name,
cg.sale_price;'''.\
format(cmid, cmid, NORMAL_STATUS.get(cmid), condition1, condition2)
res = get_df(REDSHIFT_DB_URL, sql)
return res
def get_sale_store_count_by_query(cls, **kwargs):
cmid = kwargs.get('cmid', 34)
start_at = kwargs.get(
'start_at',
arrow.now().shift(months=-2).ceil('month').date())
end_at = kwargs.get('end_at',
arrow.now().shift(months=-1).ceil('month').date())
sql = '''
select
foreign_item_id,
count(distinct foreign_store_id) as has_sell_count
from cost_{}yyyyyyyyyyyyy
where
date > '{}'
and date < '{}'
group by foreign_item_id;'''.format(cmid, start_at, end_at)
res = get_df(REDSHIFT_DB_URL, sql)
return res