def post(self): goods_id = self.get_argument("id", "") start_arg = self.get_argument("start", "") end_arg = self.get_argument("end", "") data = [] categories, start, end = generate_duration(start_arg, end_arg) goods = self.db.get("select id, short_name from goods where id=%s and deleted=0", goods_id) # 查不到商品,返回 if not goods: data.append({"name": "商品不存在", "data": format_chart_data([], start, categories)}) goods_amount = self.db.query( "select cast(i.created_at as DATE) as archive_date, sum(payment) as amount from " "item i where goods_id=%s and cast(i.created_at as DATE)>=%s " "and cast(i.created_at as DATE)<=%s " "group by archive_date order by archive_date asc", goods_id, start, end, ) # 如果有销售数据 if goods_amount: # 补全时间轴 sales_data = format_chart_data(goods_amount, start, categories) # 添加到返回的数据列表上 data.append({"name": goods.short_name, "data": sales_data}) # 没有数据,返回空数据 if not data: data.append({"name": "无数据", "data": format_chart_data([], start, categories)}) self.write(json_dumps(data, decimal_fmt=float))
def post(self): user = self.get_current_user() start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') total = self.get_argument('total', '') # 用来分辨是销售人员查询个人,还是查询所有 data = [] extend_sql = '' categories, start, end = generate_duration(start_arg, end_arg) # 没有total, 需查询销售人员关联的商户 if not total: related_suppliers = self.db.query( 'select id from supplier where sales_id=%s', user.id) ids = ','.join([str(item.id) for item in related_suppliers]) # 没有关联商户,返回空数据 if not ids: data.append({ 'name': '无数据', 'data': format_chart_data([], start, categories) }) self.write(json_dumps(data, decimal_fmt=float)) return extend_sql = ' and sp_id in (' + ids + ') ' # 该销售人员旗下所有商户的所有商品在分销商中的销售的数据按日归档 sql = 'select cast(i.created_at as DATE) as archive_date, sum(payment) as amount, distr_shop_id from item i ' \ 'where cast(i.created_at as DATE)>=%s and cast(i.created_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date, distr_shop_id order by archive_date, distr_shop_id asc ' account_sequence = self.db.query(sql, start, end) # 获得所有分销渠道信息 account_info = self.db.query( 'select id, name from distributor_shop where deleted = 0') data = [] # 生成有销售数据的分销渠道的系列, 按分销渠道,一个个查 for item in account_info: temp_list = [] for sequence in account_sequence: if sequence.distr_shop_id == item.id: temp_list.append(sequence) if temp_list: # 将有销售数据的分销渠道生成数据 data.append({ 'name': item.name, 'data': format_chart_data(temp_list, start, categories) }) # 没有数据,返回空数据 if not data: data.append({ 'name': '无数据', 'data': format_chart_data([], start, categories) }) self.write(json_dumps(data, decimal_fmt=float))
def post(self): user = self.get_current_user() supplier_id = self.get_argument('id', '') start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') total = self.get_argument('total', '') # 用来分辨是销售人员查询个人,还是查询所有 data = [] categories, start, end = generate_duration(start_arg, end_arg) # 没有total, 需查询销售人员关联的商户 if not total: # 查询商家 supplier = self.db.get( 'select short_name, id from supplier where sales_id=%s and deleted=0 ' 'and id=%s', user.id, supplier_id) else: supplier = self.db.get( 'select short_name, id from supplier where id=%s and deleted=0', supplier_id) # 商家不存在 if not supplier: data.append({ 'name': '商家不存在或者您不是该商家的关联销售人员', 'data': format_chart_data([], start, categories) }) self.write(json_dumps(data, decimal_fmt=float)) return # 对商家,根据日期统计销售 sales_amount = self.db.query( 'select cast(i.created_at as DATE) as archive_date, sum(payment) as amount ' 'from item i where sp_id=%s ' 'and cast(i.created_at as DATE)>=%s and cast(i.created_at as DATE)<=%s ' 'group by archive_date order by archive_date asc', supplier_id, start, end) # 如果有销售数据 if sales_amount: # 补全时间轴 sales_data = format_chart_data(sales_amount, start, categories) # 添加到返回的数据列表上 data.append({'name': supplier.short_name, 'data': sales_data}) # 没有数据,返回空数据 if not data: data.append({ 'name': '无数据', 'data': format_chart_data([], start, categories) }) self.write(json_dumps(data, decimal_fmt=float))
def post(self): user = self.get_current_user() start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') total = self.get_argument('total', '') # 用来分辨是销售人员查询个人,还是查询所有 data = [] extend_sql = '' categories, start, end = generate_duration(start_arg, end_arg) # 没有total, 需查询销售人员关联的商户 if not total: related_suppliers = self.db.query('select id from supplier where sales_id=%s', user.id) ids = ','.join([str(item.id) for item in related_suppliers]) # 没有关联商户,返回空数据 if not ids: data.append({'name': '无数据', 'data': format_chart_data([], start, categories)}) self.write(json_dumps(data, decimal_fmt=float)) return extend_sql = ' and sp_id in (' + ids + ') ' # 该销售人员旗下所有商户的所有商品在分销商中的销售的数据按日归档 sql = 'select cast(i.created_at as DATE) as archive_date, sum(payment) as amount, distr_shop_id from item i ' \ 'where cast(i.created_at as DATE)>=%s and cast(i.created_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date, distr_shop_id order by archive_date, distr_shop_id asc ' account_sequence = self.db.query(sql, start, end) # 获得所有分销渠道信息 account_info = self.db.query('select id, name from distributor_shop where deleted = 0') data = [] # 生成有销售数据的分销渠道的系列, 按分销渠道,一个个查 for item in account_info: temp_list = [] for sequence in account_sequence: if sequence.distr_shop_id == item.id: temp_list.append(sequence) if temp_list: # 将有销售数据的分销渠道生成数据 data.append({'name': item.name, 'data': format_chart_data(temp_list, start, categories)}) # 没有数据,返回空数据 if not data: data.append({'name': '无数据', 'data': format_chart_data([], start, categories)}) self.write(json_dumps(data, decimal_fmt=float))
def post(self): start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') # 没指定日期,默认显示7天 if not start_arg or not end_arg: today = date.today() start = today - timedelta(days=6) end = today - timedelta(days=0) else: start = datetime.strptime(start_arg, '%Y-%m-%d').date() end = datetime.strptime(end_arg, '%Y-%m-%d').date() onsale_sql = """ select count(id) as amount, cast(onsale_at as Date) as archive_date from goods_distributor_shop where status="ON_SALE" and onsale_at is not NULL and cast(onsale_at as DATE)>=%s and cast(onsale_at as DATE)<=%s group by archive_date order by archive_date asc """ offsale_sql = """ select count(id) as amount, cast(offsale_at as Date) as archive_date from goods_distributor_shop where status="OFF_SALE" and offsale_at is not NULL and cast(offsale_at as DATE)>=%s and cast(offsale_at as DATE)<=%s group by archive_date order by archive_date asc """ onsale_amount = self.db.query(onsale_sql, start, end) offsale_amount = self.db.query(offsale_sql, start, end) categories, start, end = generate_duration(start_arg, end_arg) onsale_data = format_chart_data(onsale_amount, start, categories) offsale_data = format_chart_data(offsale_amount, start, categories) data = [ { 'name': '上架商品数量', 'data': onsale_data }, { 'name': '下架商品数量', 'data': offsale_data }, ] self.write(json_dumps(data, decimal_fmt=float))
def post(self): user = self.get_current_user() supplier_id = self.get_argument('id', '') start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') total = self.get_argument('total', '') # 用来分辨是销售人员查询个人,还是查询所有 data = [] categories, start, end = generate_duration(start_arg, end_arg) # 没有total, 需查询销售人员关联的商户 if not total: # 查询商家 supplier = self.db.get('select short_name, id from supplier where sales_id=%s and deleted=0 ' 'and id=%s', user.id, supplier_id) else: supplier = self.db.get('select short_name, id from supplier where id=%s and deleted=0', supplier_id) # 商家不存在 if not supplier: data.append({'name': '商家不存在或者您不是该商家的关联销售人员', 'data': format_chart_data([], start, categories)}) self.write(json_dumps(data, decimal_fmt=float)) return # 对商家,根据日期统计销售 sales_amount = self.db.query('select cast(i.created_at as DATE) as archive_date, sum(payment) as amount ' 'from item i where sp_id=%s ' 'and cast(i.created_at as DATE)>=%s and cast(i.created_at as DATE)<=%s ' 'group by archive_date order by archive_date asc', supplier_id, start, end) # 如果有销售数据 if sales_amount: # 补全时间轴 sales_data = format_chart_data(sales_amount, start, categories) # 添加到返回的数据列表上 data.append({'name': supplier.short_name, 'data': sales_data}) # 没有数据,返回空数据 if not data: data.append({'name': '无数据', 'data': format_chart_data([], start, categories)}) self.write(json_dumps(data, decimal_fmt=float))
def post(self): goods_id = self.get_argument('id', '') start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') data = [] categories, start, end = generate_duration(start_arg, end_arg) goods = self.db.get( 'select id, short_name from goods where id=%s and deleted=0', goods_id) # 查不到商品,返回 if not goods: data.append({ 'name': '商品不存在', 'data': format_chart_data([], start, categories) }) goods_amount = self.db.query( 'select cast(i.created_at as DATE) as archive_date, sum(payment) as amount from ' 'item i where goods_id=%s and cast(i.created_at as DATE)>=%s ' 'and cast(i.created_at as DATE)<=%s ' 'group by archive_date order by archive_date asc', goods_id, start, end) # 如果有销售数据 if goods_amount: # 补全时间轴 sales_data = format_chart_data(goods_amount, start, categories) # 添加到返回的数据列表上 data.append({'name': goods.short_name, 'data': sales_data}) # 没有数据,返回空数据 if not data: data.append({ 'name': '无数据', 'data': format_chart_data([], start, categories) }) self.write(json_dumps(data, decimal_fmt=float))
def post(self): user = self.get_current_user() start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') total = self.get_argument('total', '') # 用来分辨是销售人员查询个人,还是查询所有 data = [] extend_sql = '' categories, start, end = generate_duration(start_arg, end_arg) # 没有total, 需查询销售人员关联的商户 if not total: related_suppliers = self.db.query('select id from supplier where sales_id=%s', user.id) ids = ','.join([str(item.id) for item in related_suppliers]) # 没有关联商户,返回空数据 if not ids: data.append({'name': '无数据', 'data': format_chart_data([], start, categories)}) self.write(json_dumps(data, decimal_fmt=float)) return extend_sql = ' and sp_id in (' + ids + ') ' # 该销售人员旗下所有商户的'销售'金额数据按日归档 sales_sql = 'select cast(i.created_at as DATE) as archive_date, sum(payment) as amount from item i ' \ 'where cast(i.created_at as DATE)>=%s and cast(i.created_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date order by archive_date asc' sales_amount = self.db.query(sales_sql, start, end) sales_data = format_chart_data(sales_amount, start, categories) # 该销售人员旗下所有商户的'消费'金额数据按日归档 consumed_sql = 'select cast(i.used_at as DATE) as archive_date, sum(payment) as amount from item i ' \ 'where i.used_at is not null and cast(i.used_at as DATE)>=%s and cast(i.used_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date order by archive_date asc' consumed_amount = self.db.query(consumed_sql, start, end) consumed_data = format_chart_data(consumed_amount, start, categories) # 该销售人员旗下所有商户'退款'金额数据按日归档 refund_sql = 'select cast(i.refund_at as DATE) as archive_date, sum(refund_value) as amount from item i ' \ 'where i.refund_at is not null and cast(i.refund_at as DATE)>=%s and cast(i.refund_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date order by archive_date asc' refund_amount = self.db.query(refund_sql, start, end) refund_data = format_chart_data(refund_amount, start, categories) # 刷单数据 cheat_sql = 'select cast(i.cheat_at as DATE) as archive_date, sum(payment) as amount from item i ' \ 'where i.cheat_at is not null and cast(i.cheat_at as DATE)>=%s and cast(i.cheat_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date order by archive_date asc' cheat_amount = self.db.query(cheat_sql, start, end) cheat_data = format_chart_data(cheat_amount, start, categories) # 准备回传数据 data = [ { 'name': '销售金额', 'data': sales_data }, { 'name': '消费金额', 'data': consumed_data }, { 'name': '退款金额', 'data': refund_data }, { 'name': '刷单金额', 'data': cheat_data }, ] self.write(json_dumps(data, decimal_fmt=float))
def post(self): # 获得Post参数 start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') summary_type = self.get_argument('summary_type', '') supplier_input = self.get_argument('supplier_input', '') supplier_id = self.current_user.supplier_id categories, start, end = generate_duration(start_arg, end_arg) # 初始化共同参数 common_sql = ' cast(i.used_at as Date) as archive_date, sum(i.sales_price) as amount from item i ' \ 'where used_at is not null and i.sp_id=%s and cast(i.used_at as Date) >= %s ' \ 'and cast(i.used_at as Date) <= %s ' group_sql = ' group by archive_date ' order_sql = ' order by archive_date asc ' params = [supplier_id, start, end] data = [] # 用来存储返回的数据 # 准备不同请求的数据 if summary_type == 'goods_sales': goods_sql = 'select id, short_name from goods where supplier_id=%s ' goods_params = [supplier_id] # 根据输入的筛选条件,选出相应的goods id if supplier_input: goods_sql += ' and short_name like %s' goods_params.append('%' + supplier_input + '%') goods = self.db.query(goods_sql, *goods_params) sql = 'select i.goods_id, ' + common_sql if goods: sql += ' and i.goods_id in (' + ','.join( [str(i.id) for i in goods]) + ') ' sql = sql + group_sql + ', i.goods_id ' + order_sql result = self.db.query(sql, *params) for g in goods: current_line = [] for r in result: if r.goods_id == g.id: current_line.append(r) if current_line: data.append({ 'name': g.short_name, 'data': format_chart_data(current_line, start, categories) }) elif summary_type == 'supplier_shop': shop_sql = 'select id, name from supplier_shop where supplier_id=%s ' shop_params = [supplier_id] # 根据输入的门店筛选 if supplier_input: shop_sql += ' and name like %s' shop_params.append('%' + supplier_input + '%') shops = self.db.query(shop_sql, *shop_params) sql = 'select i.sp_shop_id, ' + common_sql if shops: sql += ' and i.sp_shop_id in (' + ','.join( [str(i.id) for i in shops]) + ') ' sql = sql + group_sql + ', i.sp_shop_id ' + order_sql result = self.db.query(sql, *params) for s in shops: current_line = [] for r in result: if r.sp_shop_id == s.id: current_line.append(r) if current_line: data.append({ 'name': s.name, 'data': format_chart_data(current_line, start, categories) }) elif summary_type == 'distr_shop': distr_sql = 'select id, name from distributor_shop where 1=1 ' distr_params = [] if supplier_input: distr_sql += ' and name like %s' distr_params.append('%' + supplier_input + '%') if distr_params: distr = self.db.query(distr_sql, *distr_sql) else: distr = self.db.query(distr_sql) sql = 'select i.distr_shop_id,' + common_sql if distr: sql += ' and i.distr_shop_id in (' + ','.join( [str(i.id) for i in distr]) + ') ' sql = sql + group_sql + ', i.distr_shop_id ' + order_sql result = self.db.query(sql, *params) for d in distr: current_line = [] for r in result: if r.distr_shop_id == d.id: current_line.append(r) if current_line: data.append({ 'name': d.name, 'data': format_chart_data(current_line, start, categories) }) # 没有数据,返回空数据 if not data: data.append({ 'name': '无数据', 'data': format_chart_data([], start, categories) }) self.write(json_dumps(data, decimal_fmt=float)) pass
def post(self): user = self.get_current_user() start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') total = self.get_argument('total', '') # 用来分辨是销售人员查询个人,还是查询所有 data = [] extend_sql = '' categories, start, end = generate_duration(start_arg, end_arg) # 没有total, 需查询销售人员关联的商户 if not total: related_suppliers = self.db.query( 'select id from supplier where sales_id=%s', user.id) ids = ','.join([str(item.id) for item in related_suppliers]) # 没有关联商户,返回空数据 if not ids: data.append({ 'name': '无数据', 'data': format_chart_data([], start, categories) }) self.write(json_dumps(data, decimal_fmt=float)) return extend_sql = ' and sp_id in (' + ids + ') ' # 该销售人员旗下所有商户的'销售'金额数据按日归档 sales_sql = 'select cast(i.created_at as DATE) as archive_date, sum(payment) as amount from item i ' \ 'where cast(i.created_at as DATE)>=%s and cast(i.created_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date order by archive_date asc' sales_amount = self.db.query(sales_sql, start, end) sales_data = format_chart_data(sales_amount, start, categories) # 该销售人员旗下所有商户的'消费'金额数据按日归档 consumed_sql = 'select cast(i.used_at as DATE) as archive_date, sum(payment) as amount from item i ' \ 'where i.used_at is not null and cast(i.used_at as DATE)>=%s and cast(i.used_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date order by archive_date asc' consumed_amount = self.db.query(consumed_sql, start, end) consumed_data = format_chart_data(consumed_amount, start, categories) # 该销售人员旗下所有商户'退款'金额数据按日归档 refund_sql = 'select cast(i.refund_at as DATE) as archive_date, sum(refund_value) as amount from item i ' \ 'where i.refund_at is not null and cast(i.refund_at as DATE)>=%s and cast(i.refund_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date order by archive_date asc' refund_amount = self.db.query(refund_sql, start, end) refund_data = format_chart_data(refund_amount, start, categories) # 刷单数据 cheat_sql = 'select cast(i.cheat_at as DATE) as archive_date, sum(payment) as amount from item i ' \ 'where i.cheat_at is not null and cast(i.cheat_at as DATE)>=%s and cast(i.cheat_at as DATE)<=%s ' \ + extend_sql + 'group by archive_date order by archive_date asc' cheat_amount = self.db.query(cheat_sql, start, end) cheat_data = format_chart_data(cheat_amount, start, categories) # 准备回传数据 data = [ { 'name': '销售金额', 'data': sales_data }, { 'name': '消费金额', 'data': consumed_data }, { 'name': '退款金额', 'data': refund_data }, { 'name': '刷单金额', 'data': cheat_data }, ] self.write(json_dumps(data, decimal_fmt=float))
def post(self): # 获得Post参数 start_arg = self.get_argument('start', '') end_arg = self.get_argument('end', '') summary_type = self.get_argument('summary_type', '') supplier_input = self.get_argument('supplier_input', '') supplier_id = self.current_user.supplier_id categories, start, end = generate_duration(start_arg, end_arg) # 初始化共同参数 common_sql = ' cast(i.used_at as Date) as archive_date, sum(i.sales_price) as amount from item i ' \ 'where used_at is not null and i.sp_id=%s and cast(i.used_at as Date) >= %s ' \ 'and cast(i.used_at as Date) <= %s ' group_sql = ' group by archive_date ' order_sql = ' order by archive_date asc ' params = [supplier_id, start, end] data = [] # 用来存储返回的数据 # 准备不同请求的数据 if summary_type == 'goods_sales': goods_sql = 'select id, short_name from goods where supplier_id=%s ' goods_params = [supplier_id] # 根据输入的筛选条件,选出相应的goods id if supplier_input: goods_sql += ' and short_name like %s' goods_params.append('%'+supplier_input+'%') goods = self.db.query(goods_sql, *goods_params) sql = 'select i.goods_id, ' + common_sql if goods: sql += ' and i.goods_id in (' + ','.join([str(i.id) for i in goods]) + ') ' sql = sql + group_sql + ', i.goods_id ' + order_sql result = self.db.query(sql, *params) for g in goods: current_line = [] for r in result: if r.goods_id == g.id: current_line.append(r) if current_line: data.append({'name': g.short_name, 'data': format_chart_data(current_line, start, categories)}) elif summary_type == 'supplier_shop': shop_sql = 'select id, name from supplier_shop where supplier_id=%s ' shop_params = [supplier_id] # 根据输入的门店筛选 if supplier_input: shop_sql += ' and name like %s' shop_params.append('%' + supplier_input + '%') shops = self.db.query(shop_sql, *shop_params) sql = 'select i.sp_shop_id, ' + common_sql if shops: sql += ' and i.sp_shop_id in (' + ','.join([str(i.id) for i in shops]) + ') ' sql = sql + group_sql + ', i.sp_shop_id ' + order_sql result = self.db.query(sql, *params) for s in shops: current_line = [] for r in result: if r.sp_shop_id == s.id: current_line.append(r) if current_line: data.append({'name': s.name, 'data': format_chart_data(current_line, start, categories)}) elif summary_type == 'distr_shop': distr_sql = 'select id, name from distributor_shop where 1=1 ' distr_params = [] if supplier_input: distr_sql += ' and name like %s' distr_params.append('%' + supplier_input + '%') if distr_params: distr = self.db.query(distr_sql, *distr_sql) else: distr = self.db.query(distr_sql) sql = 'select i.distr_shop_id,' + common_sql if distr: sql += ' and i.distr_shop_id in (' + ','.join([str(i.id) for i in distr]) + ') ' sql = sql + group_sql + ', i.distr_shop_id ' + order_sql result = self.db.query(sql, *params) for d in distr: current_line = [] for r in result: if r.distr_shop_id == d.id: current_line.append(r) if current_line: data.append({'name': d.name, 'data': format_chart_data(current_line, start, categories)}) # 没有数据,返回空数据 if not data: data.append({'name': '无数据', 'data': format_chart_data([], start, categories)}) self.write(json_dumps(data, decimal_fmt=float)) pass