def all_product():
# result = db_session.execute('''SELECT product_id,name,unit_price,image_url,description
# FROM products''').fetchall()
# formatted_result = [{'product_id':item[0],'name':item[1],'unit_price':item[2],'image_url':item[3],'description':item[4]} for item in result]
# return(jsonify(formatted_result))
# result is list of tuples
result = db_session.execute('''SELECT prod_json FROM products''' ).fetchall()
formatted_result = [json.loads(item[0]) for item in result]
return (jsonify(formatted_result))
def get_product(product_id):
# stmt = text('''SELECT product_id,name,unit_price,image_url,description
# FROM products WHERE product_id = :id ''').bindparams(id=product_id)
stmt = text('''SELECT p.prod_json FROM products p WHERE p.prod_json.product_id = :id''').bindparams(id=product_id)
result = db_session.execute(stmt).first()
if result:
# formatted_result = json.loads(result[0])
# formatted_result = {'product_id':result[0],'name':result[1],'unit_price':result[2],'image_url':result[3],'description':result[4]}
return jsonify(json.loads(result[0]))
else:
raise InvalidUsage("Product does not Exist",404)
def get_prod_inventory(product_id):
stmt = text("SELECT product_id, quantity FROM inventory WHERE product_id = :id ").bindparams(id=product_id)
result = db_session.execute(stmt).first()
if result:
formatted_result = {'product_id':result[0],'quantity':result[1]}
if formatted_result.get('quantity') > 0:
return (jsonify(formatted_result))
else:
return (jsonify(message = 'Out of Stock'))
else:
return (jsonify(message = 'Product does not exist'))
def total_views():
sample_data = [{
'ga_date': '2020-05-01',
'product_id': 'BGB-US-001',
'page_views': 150
}, {
'ga_date': '2020-05-01',
'product_id': 'BGB-US-002',
'page_views': 200
}, {
'ga_date': '2020-05-01',
'product_id': 'BGB-US-003',
'page_views': 400
}, {
'ga_date': '2020-05-01',
'product_id': 'BGB-US-004',
'page_views': 25
}, {
'ga_date': '2020-05-01',
'product_id': 'BGB-US-005',
'page_views': 122
}, {
'ga_date': '2020-05-01',
'product_id': 'BGB-US-006',
'page_views': 170
}, {
'ga_date': '2020-05-01',
'product_id': 'BGB-US-007',
'page_views': 80
}]
# result is list of tuples
result = db_session.execute(
'''SELECT sum(page_views) FROM GA_SINK''').fetchall()
# formatted_result = [{'date':item[0],'product_id':item[1],'page_views':str(item[2])} for item in result]
# return (jsonify(formatted_result))
return (jsonify(result[0][0]))
def get_all_inventory():
# would users even need this api endpoint?
result = db_session.execute("SELECT product_id, quantity FROM inventory").fetchall()
formatted_result = [{'product_id':item[0],'quantity':item[1]} for item in result]
return(jsonify(formatted_result))
def total_sales():
# result is list of tuples
# result = db_session.execute('''SELECT sum(amount) FROM product_sales''' ).fetchall()
result = db_session.execute(
'''SELECT sum(amount) FROM demo_sales''').fetchall()
return (jsonify(result[0][0]))
def custom_reports(report_id):
if report_id == 'A':
# result = db_session.execute('''select ga_date,sum(page_views),floor(dbms_random.value(2000, 6000)) as sales
# from ga_sink
# group by ga_date''' ).fetchall()
result = db_session.execute(
'''select T1.ga_date,T1.page_views, T2.total_sale
from (select ga_date,sum(page_views) as page_views from ga_sink group by ga_date) T1
join (select sale_date,sum(amount) as total_sale from demo_sales group by sale_date) T2
on T1.ga_date=T2.sale_date''').fetchall(
)
# result = db_session.execute('''select T1."date",T1.page_views, T2.total_sale
# from (select "date",sum(page_views) as page_views from test group by "date") T1
# join (select sale_date,sum(amount) as total_sale from demo_sales group by sale_date) T2
# on T1."date"=T2.sale_date''' ).fetchall()
print(result)
test = pd.DataFrame(result,
columns=['date', 'page_views', 'total_sale'])
test['date'] = pd.to_datetime(test['date'])
test.set_index(keys=['date'], inplace=True)
test.sort_index(inplace=True)
cds = ColumnDataSource(test)
p = Figure(plot_width=1000,
plot_height=500,
title="Sales Vs Views",
y_range=Range1d(start=2500, end=33000),
x_axis_type='datetime',
x_axis_label='Date',
y_axis_label='Revenue($)')
l1 = p.line('date',
'page_views',
source=cds,
line_color=d3['Category10'][10][0],
line_width=5,
legend="Page Views")
l2 = p.line('date',
'total_sale',
source=cds,
line_color=d3['Category10'][10][1],
line_width=5,
legend="Revenue")
p.extra_y_ranges = {"foo": Range1d(start=0, end=6000)}
p.add_layout(
LinearAxis(y_range_name='foo', axis_label="Number of Views"),
'right')
p.legend.location = "bottom_right"
p.background_fill_color = "beige"
p.background_fill_alpha = 0.5
p.border_fill_color = "#F8F8FF"
p.add_tools(
HoverTool(
renderers=[l1],
tooltips=[
('date',
'@date{%F}'), # use @{ } for field names with spaces
('views', '@page_views'),
],
formatters={
'date':
'datetime', # use 'datetime' formatter for 'date' field
# use default 'numeral' formatter for other fields
},
# display a tooltip whenever the cursor is vertically in line with a glyph
mode='vline'))
p.add_tools(
HoverTool(
renderers=[l2],
tooltips=[
# ( 'date', '@date{%F}' ),
('revenue', '$@{total_sale}'
), # use @{ } for field names with spaces
],
formatters={
# 'date' : 'datetime', # use 'datetime' formatter for 'date' field
'revenue':
'printf', # use 'printf' formatter for 'adj close' field
# use default 'numeral' formatter for other fields
},
# display a tooltip whenever the cursor is vertically in line with a glyph
mode='vline'))
return json.dumps(json_item(p))
if report_id == "B":
result = db_session.execute(
'''select product_id,sum(page_views) as views
from ga_sink
group by product_id
order by views desc ''').fetchall()
# result = db_session.execute('''select product_id,sum(page_views) as views
# from test
# group by product_id
# order by views desc ''' ).fetchall()
test = pd.DataFrame(result, columns=['product_id', 'page_views'])
test.set_index(keys=['product_id'], inplace=True)
cds = ColumnDataSource(test)
p = Figure(x_range=cds.data['product_id'],
plot_height=350,
title="Top Products by Views",
tools="")
p.vbar(x='product_id',
top='page_views',
source=cds,
width=0.9,
fill_color=factor_cmap(field_name='product_id',
palette=d3['Category10'][10],
factors=cds.data['product_id']))
p.xgrid.grid_line_color = None
p.y_range.start = 0
p.background_fill_color = "beige"
p.background_fill_alpha = 0.5
p.border_fill_color = "#F8F8FF"
return json.dumps(json_item(p))
if report_id == "C":
# cdata= [{'product_id':'BGB-US-001','total_sale': random.randint(1000,8000)},
# {'product_id':'BGB-US-002','total_sale': random.randint(1000,8000)},
# {'product_id':'BGB-US-003','total_sale': random.randint(1000,8000)},
# {'product_id':'BGB-US-004','total_sale': random.randint(1000,8000)},
# {'product_id':'BGB-US-005','total_sale': random.randint(1000,8000)},
# {'product_id':'BGB-US-006','total_sale': random.randint(1000,8000)},
# {'product_id':'BGB-US-007','total_sale': random.randint(1000,8000)}]
cdata = db_session.execute('''select product_id,sum(amount)
from demo_sales
group by product_id''').fetchall()
c = pd.DataFrame(cdata, columns=['product_id', 'amount'])
c.rename(columns={"amount": "total_sale"}, inplace=True)
print(c)
c.set_index(keys=['product_id'], inplace=True)
c['angle'] = c['total_sale'] / c['total_sale'].sum() * 2 * pi
c['color'] = d3['Category10'][10][len(c) - 1::-1]
c['percent'] = round(c['total_sale'] / c['total_sale'].sum() * 100, 0)
cds = ColumnDataSource(c)
p = Figure(plot_height=350,
title="Revenue Breakdown by Product",
tools="hover",
tooltips="@product_id: @percent %",
x_range=(-0.5, 1.0))
p.wedge(x=0,
y=1,
radius=0.4,
start_angle=cumsum('angle', include_zero=True),
end_angle=cumsum('angle'),
line_color="white",
fill_color='color',
legend='product_id',
source=cds)
p.axis.axis_label = None
p.axis.visible = False
p.grid.grid_line_color = None
p.background_fill_color = "beige"
p.background_fill_alpha = 0.5
p.border_fill_color = "#F8F8FF"
return json.dumps(json_item(p))
def add_to_db(params, db_session, current_order):
update_stmt = update(Inventory).where(
Inventory.product_id == bindparam('prod')).values(
{Inventory.quantity: Inventory.quantity - bindparam('qty')})
db_session.execute(update_stmt, params)
db_session.add(current_order)
