def get_orders(store_id, user_id): sql = """ SELECT item.name, order_item.num, order_item.subtotal, orders.order_time FROM shopplus_order as orders LEFT JOIN shopplus_orderitem as order_item ON orders.id=order_item.order_id LEFT JOIN shopplus_item as item ON order_item.item_id=item.id WHERE orders.store_id={store_id} AND orders.user_id={user_id} AND orders.status=1 ORDER BY orders.order_time DESC """.format(store_id=store_id, user_id=user_id) cur = connection.cursor() cur.execute(sql) orders = sql_utils.dictfetchall(cur) return orders
def get_cart_items(self): result = [] with connection.cursor() as cursor: cursor.execute(queries.CART_ITEMS_SQL, [self.user_id, self.store_id]) result = sql_utils.dictfetchall(cursor) return sorted(result, key=op.itemgetter('add_time'), reverse=True)
def get_cart_sale_series_items(sid, bagman_customer_id): sql = '''SELECT item.*, bagmanitem.quantity FROM recommendorder_bagmanitem AS bagmanitem LEFT JOIN recommendorder_item AS item ON bagmanitem.item_id=item.id LEFT JOIN recommendorder_series AS series ON series.category_id=item.related_category_id INNER JOIN recommendorder_seriesitem AS seriesitem ON seriesitem.series_id = series.id AND seriesitem.id=item.series_item_id WHERE series.id=%(sid)s AND bagmanitem.add_time<=now() AND bagmanitem.add_time>=date_trunc('day', now()) AND bagmanitem.customer_id=%(bagman_customer_id)s; ''' cur = connection.cursor() cur.execute(sql, {'sid': sid, 'bagman_customer_id': bagman_customer_id}) results = sql_utils.dictfetchall(cur) return results
def get_guides(brand_id, type): if (type == ''): sql = ''' SELECT a.*, b.store_name, b.number FROM dailystatement_guide AS a LEFT JOIN dailystatement_store AS b ON a.store_id=b.id WHERE a.brand_id={id} AND a.status != 2 ORDER BY a.update_time DESC '''.format(id=brand_id) else: sql = ''' SELECT a.*, b.store_name, b.number FROM dailystatement_guide AS a LEFT JOIN dailystatement_store AS b ON a.store_id=b.id WHERE a.brand_id={id} AND a.status={type} ORDER BY a.update_time DESC '''.format(id=brand_id, type=type) cur = connection.cursor() cur.execute(sql) guides = sql_utils.dictfetchall(cur) for guide in guides: guide['create_time'] = str(guide['create_time']) return guides
def get_basket_week(store_id): start_date = (datetime.date.today() - datetime.timedelta(days=70)).strftime('%Y-%m-%d') end_date = datetime.date.today().strftime('%Y-%m-%d') sql = ''' SELECT count(sd.id) as count_id,sum(sd.num) as sum_num,to_char(sd.date,'YYYY-MM-DD') as date FROM summary_dailystoreitemsummary as sd WHERE sd.date BETWEEN to_date('%(start_date)s','YYYY-MM-DD') AND to_date('%(end_date)s','YYYY-MM-DD') AND store_id = '%(store_id)s' GROUP BY date ''' % { 'start_date': start_date, 'end_date': end_date, 'store_id': store_id } cur = connection.cursor() cur.execute(sql) results = sql_utils.dictfetchall(cur) week_results = {} for result in results: myDate = result['date'] sum_num = result['sum_num'] count_id = result['count_id'] week = datetime.datetime.strptime(myDate, "%Y-%m-%d").weekday() if week not in week_results: week_results[week] = {'sum': 0.0, 'count': 0.0} week_results[week]['sum'] += sum_num week_results[week]['count'] += count_id for week, week_result in week_results.items(): week_results[week][ 'basket'] = week_results[week]['sum'] / week_results[week]['count'] return week_results
def get_cart_sale_category_items(cid, bagman_customer_id, cat_id): sql = '''SELECT temp.num selected_num, item.*, bagmanitem.quantity FROM recommendorder_bagmanitem AS bagmanitem LEFT JOIN recommendorder_item AS item ON bagmanitem.item_id=item.id LEFT JOIN (SELECT cartitem.item_id, num FROM recommendorder_bagmancartitem AS cartitem INNER JOIN recommendorder_bagmancart AS cart ON cartitem.cart_id=cart.id WHERE cart.customer_id=%(customer_id)s ) AS temp ON temp.item_id=item.id WHERE bagmanitem.customer_id=%(bagman_customer_id)s AND bagmanitem.add_time<=now() AND bagmanitem.add_time>=date_trunc('day', now()) AND item.related_category_id=%(category_id)s; ''' cur = connection.cursor() cur.execute(sql, {'customer_id': cid, 'category_id': cat_id, 'bagman_customer_id': bagman_customer_id}) results = sql_utils.dictfetchall(cur) return results
def get_prices_category(store_id,start_date,end_date): sql = """ SELECT (case when sum(num) = 0 then -1 else sum(sales)/sum(num) end) as price_avg , category , item_id FROM summary_dailystoreitemsummary WHERE store_id = '%(store_id)s' AND date BETWEEN to_date('%(start_date)s','YYYY-MM-DD') AND to_date('%(end_date)s','YYYY-MM-DD') AND sales is not null AND num is not null AND category is not null GROUP BY item_id,category """ % {'start_date': start_date, 'end_date': end_date, 'store_id': store_id} cur = connection.cursor() cur.execute(sql) results = sql_utils.dictfetchall(cur) out = {} for result in results: category = result['category'] price = math.ceil(result['price_avg']) if category not in out: out[category] = {} if price not in out[category]: out[category][price] = 0 out[category][price] += 1 return out
def get(): params = request.GET user = request.user store = models.Store.objects.get(boss=user) item_id = int(params.get('item_id')) shelf_id = params.get('shelf_id') logs = models.ItemPostionExchangeLog.objects.filter(shelf_id=shelf_id, item_id=item_id) logs = logs[-5:] log_count = logs.count() results = [] rebuy_start = timezone.now() - datetime.timedelta(15) rebuy_percent = pay_util.item_rebuy_percent(item_id, store.id, rebuy_start, timezone.now()) for i in range(log_count): if i == log_count: start_time = logs[i].create_time end_time = timezone.now() else: start_time = logs[i].create_time end_time = logs[i + 1].create_time sql = """ SELECT receipt_item_id as item_id, max(item_name) as name, sum(quantity) as sales, (max(price) - max(purchase_price)) as profit, wx_saletime FROM cm_cvs_goodsflow WHERE receipt_item_id='{item_id}' AND wx_store_id='{store_id}' AND wx_saletime>={start_time} AND wx_saletime<={end_time} GROUP BY receipt_item_id; """.format(store_id=store.id, start_time=start_time, end_time=end_time, item_id=item_id) cur = connection.cursor() cur.execute(sql) shelf_results = sql_utils.dictfetchall(cur) shelf_result = shelf_results[0] time_space = float((end_time - start_time).days) shelf_result['row'] = logs[0].row shelf_result['column'] = logs[0].column shelf_result['sales'] = float('%.2f' % (shelf_result['sales'] / time_space)) results.append(shelf_result) results = sorted(results, key=lambda dic: dic['wx_saletime']) return {'data': {'sales': results, 'rebuy_percent': rebuy_percent}}
def get_cart_sale_buyed_categorys(customer_id): sql = '''SELECT item.category, item.related_category_id, item.series_item_id, bagmanitem.quantity, series.name series_name, series.id series_id, seriesitem.name series_item_name FROM recommendorder_bagmanitem AS bagmanitem LEFT JOIN recommendorder_item AS item ON bagmanitem.item_id=item.id LEFT JOIN recommendorder_series AS series ON item.related_category_id=series.category_id LEFT JOIN recommendorder_seriesitem AS seriesitem ON series.id = seriesitem.series_id WHERE bagmanitem.customer_id=%(customer_id)s AND bagmanitem.add_time<=now() AND bagmanitem.add_time>=date_trunc('day', now()); ''' cur = connection.cursor() cur.execute(sql, {'customer_id': customer_id}) results = sql_utils.dictfetchall(cur) category_array = [] category_temp = [] series_temp = {} for result in results: category_dict = {} if result['category'] not in category_temp: category_temp.append(result['category']) category_dict['name'] = result['category'] category_dict['id'] = result['related_category_id'] if result['series_name']: category_dict['series'] = [{'name': result['series_name'], 'id':result['series_id']}] else: category_dict['series'] = [] series_temp[result['category']] = category_dict['series'] category_array.append(category_dict) else: series = series_temp[result['category']] flag = True for serie in series: if result['series_name'] == serie.get('name'): flag = False break if flag and result['series_name']: series.append({'name': result['series_name'], 'id': result['series_id']}) category_dict['series'] = series return category_array
def get_all_goods(brand_id): sql = ''' SELECT a.*, b.name as group_name FROM dailystatement_goods AS a LEFT JOIN dailystatement_groups AS b ON a.groups_id=b.id WHERE b.brand_id=%(id)s;''' cur = connection.cursor() cur.execute(sql, params={'id': brand_id}) goods_list = sql_utils.dictfetchall(cur) return goods_list
def get_activity_data(brand_id, start_time, end_time, args): sql = ''' SELECT guide.name as guide, store.store_name as store, goods.name as goods, redpack.total_amount / 100 as money, log.num, (log.add_time AT TIME ZONE 'CCT') as add_time, log.store_receipts as img FROM advertisement_redpackrecord as redpack LEFT JOIN advertisement_weixinqrcode as qr ON redpack.qrcode_id=qr.id LEFT JOIN dailystatement_rebatelog as log ON qr.qrcode=log.qrcode LEFT JOIN dailystatement_itemrebate as itemrebate ON log.item_rebate_id=itemrebate.id LEFT JOIN dailystatement_goods as goods ON itemrebate.item_id=goods.id LEFT JOIN dailystatement_guide as guide ON log.guide_id=guide.id LEFT JOIN dailystatement_store as store ON guide.store_id=store.id WHERE redpack.redpack_name_id=19 AND redpack.status='SUCCESS' AND log.add_time::date >='{start_time}' AND log.add_time::date <='{end_time}' AND log.qrcode is not null; '''.format(start_time=start_time, end_time=end_time) cur = connection.cursor() cur.execute(sql) data = sql_utils.dictfetchall(cur) return data
def get_store_nearby(distance, store_id): #item_geo sql = """ select lat,lng from store_store where store_id ='%(store_id)s' """ % { 'store_id': store_id } cur = connection.cursor() cur.execute(sql) results = sql_utils.dictfetchall(cur) input_geo = {} if len(results) == 1: input_geo = results[0] #near_by items sql = """ select store_id,lat,lng from store_store where lat between '%(lat_min)s' and '%(lat_max)s' and lng between '%(lng_min)s' and '%(lng_max)s' """ % { 'lat_min': input_geo['lat'] - 1, 'lat_max': input_geo['lat'] + 1, 'lng_min': input_geo['lng'] - 1, 'lng_max': input_geo['lng'] + 1 } cur = connection.cursor() cur.execute(sql) results = sql_utils.dictfetchall(cur) items = [] for item in results: item_geo = {'lat': item['lat'], 'lng': item['lng']} dis = distance_bd(input_geo, item_geo) if dis < distance: item['dis'] = dis items.append(item) return items
def get_checkin_profile(brand_id): sql = """ SELECT guide.name, guide.telephone, log.exception_type FROM dailystatement_guide AS guide LEFT JOIN dailystatement_checkinexceptionlog AS log ON guide.id=log.guide_id WHERE guide.brand_id=%(brand_id)s AND log.add_time > date_trunc('day', now()) AND log.add_time <= now() """ cur = connection.cursor() cur.execute(sql, params={'brand_id': brand_id}) infos = sql_utils.dictfetchall(cur) late = [] late_count = 0 leave_early = [] leave_early_count = 0 guide_count = models.Guide.objects.filter(brand_id=brand_id).count() for info in infos: if info['exception_type'] == 1: late.append(info) late_count += 1 elif info['exception_type'] == 2: leave_early.append(info) leave_early_count += 1 late_percent = '%.2f' % (float(late_count) / guide_count * 100) leave_early_percent = '%.2f' % (float(leave_early_count) / guide_count * 100) if len(late) > 8: late = late[0:8] if len(leave_early) > 8: leave_early = leave_early[0:8] return { 'lates': late, 'leave_earlies': leave_early, 'late_count': late_count, 'leave_early_count': leave_early_count, 'late_percent': late_percent, 'leave_early_percent': leave_early_percent }
def get_basket_salesper(store_id, start_date, end_date): sql = ''' SELECT (case when sales_all.sum_sales_all = 0 then 0 else (sales_top.sum_sales_top+0.0)/sales_all.sum_sales_all end ) as sales_per, basket,sales_top.date_out as out_date FROM ( (SELECT sum(sd.sales) as sum_sales_top,to_char(sd.date,'YYYY-MM-DD') as date_out,sum(sd.num)/count(sd.id) as basket FROM ( SELECT sum(sales) as sum_sales,item_id FROM summary_dailystoreitemsummary WHERE date BETWEEN to_date('%(start_date)s','YYYY-MM-DD') AND to_date('%(end_date)s','YYYY-MM-DD') AND store_id = '%(store_id)s' GROUP BY item_id ORDER BY sum_sales desc limit 20 ) as top, summary_dailystoreitemsummary as sd WHERE sd.item_id = top.item_id GROUP BY date_out ) as sales_top JOIN ( SELECT sum(sales) as sum_sales_all,to_char(date,'YYYY-MM-DD') as my_date FROM summary_dailystoreitemsummary WHERE date BETWEEN to_date('%(start_date)s','YYYY-MM-DD') AND to_date('%(end_date)s','YYYY-MM-DD') AND store_id = '%(store_id)s' group by my_date )sales_all ON sales_top.date_out = sales_all.my_date ) ORDER BY sales_top.date_out; ''' % { 'start_date': start_date, 'end_date': end_date, 'store_id': store_id } cur = connection.cursor() cur.execute(sql) results = sql_utils.dictfetchall(cur) return results
def get_stores(brand_id): sql = '''SELECT a.*, b.name chain_name FROM dailystatement_store AS a LEFT JOIN dailystatement_chain AS b ON a.chain_id=b.id WHERE a.brand_id=%(id)s;''' cur = connection.cursor() cur.execute(sql, params={'id': brand_id}) stores = sql_utils.dictfetchall(cur) for store in stores: store['create_time'] = str(store['create_time']) if store['lat'] and store['lng']: store['lat'] = float(store['lat']) store['lng'] = float(store['lng']) return stores
def get_store_sales_category(store_id, start_date, end_date): sql = """ select sum(sales) as sum_sales,category from summary_dailystoreitemsummary where store_id = '%(store_id)s' and category is not null and date between to_date('%(start_date)s','YYYY-MM-DD') and to_date('%(end_date)s','YYYY-MM-DD') group by category """ % { 'start_date': start_date, 'end_date': end_date, 'store_id': store_id } cur = connection.cursor() cur.execute(sql) results = sql_utils.dictfetchall(cur) return results
def get_realtime_checkin(brand_id, type, start_time, end_time): datelist = pandas.date_range(start_time, end_time) results = [] for date in datelist: date = str(date)[:10] sql = ''' SELECT guide.name as guide, store.store_name as store, guide.telephone, (checkin.time AT TIME ZONE 'CCT') as time, checkin.phone_model as phone, guide.status FROM dailystatement_guide as guide LEFT JOIN dailystatement_store as store ON guide.store_id = store.id LEFT JOIN ( SELECT * FROM dailystatement_realtimecheck WHERE (time AT TIME ZONE 'CCT')::date = '{date}' ) AS checkin ON guide.id = checkin.guide_id WHERE guide.status = {type} AND guide.brand_id = {brand_id} ORDER BY time '''.format(date=date, type=type, brand_id=brand_id) cur = connection.cursor() cur.execute(sql) data = sql_utils.dictfetchall(cur) for result in data: result['date'] = date results.extend(data) return results
def get_basket_coefficient(store_id, start_date, end_date): sql = ''' SELECT count(sd.id) as count_id,sum(sd.num) as sum_num, sum(sd.sales) as sum_sales ,ss.name ,sd.category, sum(sd.num)/count(sd.id) as basket,sum(sd.sales)/sum(sd.num) as price_avg FROM summary_dailystoreitemsummary as sd,store_storeitem as ss WHERE sd.date BETWEEN to_date('%(start_date)s','YYYY-MM-DD') AND to_date('%(end_date)s','YYYY-MM-DD') AND sd.store_id = '%(store_id)s' AND sd.item_id = ss.id AND category is not null GROUP BY sd.item_id,ss.name,category ORDER BY category,basket; ''' % { 'start_date': start_date, 'end_date': end_date, 'store_id': store_id } cur = connection.cursor() cur.execute(sql) results = sql_utils.dictfetchall(cur) return results
def get_commission(brand_id, start_time, end_time): sql = ''' SELECT a.id, a.store_name, number, commission,e.name as guide FROM dailystatement_store as a LEFT JOIN (SELECT sum(b.commission*c.sales) as commission,c.store_id FROM dailystatement_goods as b RIGHT JOIN dailystatement_statement as c ON b.id = c.goods_id WHERE c.create_time::date >= %(start_time)s and c.create_time::date <= %(end_time)s GROUP BY c.store_id ) AS d ON a.id = d.store_id LEFT JOIN dailystatement_guide as e ON a.id = e.store_id WHERE a.brand_id = %(brand_id)s ''' cur = connection.cursor() cur.execute(sql, params={ 'start_time': start_time, 'end_time': end_time, 'brand_id': brand_id }) commission_list = sql_utils.dictfetchall(cur) return commission_list
def get_map_data(brand_id): sql = ''' SELECT guide.name, guide.telephone, store.store_address, store.store_name, store.lng, store.lat, store.id store_id FROM dailystatement_guide AS guide LEFT JOIN dailystatement_store AS store ON guide.store_id = store.id WHERE guide.brand_id = %(brand_id)s AND store.lat IS NOT NULL AND store.lng IS NOT NULL; ''' cur = connection.cursor() cur.execute(sql, params={'brand_id': brand_id}) infos = sql_utils.dictfetchall(cur) return infos
def get_sales_detail_data(brand_id, start_time, end_time, args, isnormal): if (isnormal == 'true'): sql = ''' SELECT b.name as guide, c.name as goods, c.barcode as barcode, a.sales, (a.create_time AT TIME ZONE 'CCT')::date as add_time, d.store_name as store FROM dailystatement_statement as a LEFT JOIN dailystatement_guide as b ON a.operator_id = b.id LEFT JOIN dailystatement_goods as c ON a.goods_id = c.id LEFT JOIN dailystatement_store as d ON a.store_id = d.id WHERE a.create_time >= '{start_time}' AND a.create_time <= '{end_time}' AND b.status = 0 '''.format(start_time=start_time, end_time=end_time) else: sql = ''' SELECT a.name, a.telephone, c.store_name, a.status, a.create_time::date as create_time FROM dailystatement_guide as a LEFT JOIN (SELECT operator_id FROM dailystatement_statement WHERE (create_time AT TIME ZONE 'CCT')::DATE >= '{start_time}' AND (create_time AT TIME ZONE 'CCT')::DATE <= '{end_time}' )AS b ON a.id = b.operator_id LEFT JOIN dailystatement_store as c ON a.store_id = c.id WHERE b.operator_id is null AND a.status = 0 AND (a.create_time AT TIME ZONE 'CCT') < '{start_time}' '''.format(start_time=start_time, end_time=end_time) cur = connection.cursor() cur.execute(sql) data = sql_utils.dictfetchall(cur) return data
def loss_analysis(start_date, end_date, item_id): lose_days = 0 lose_count = 0.0 lose_dates = [] maxPer = 0.5 maxPerSecond = 0.001 maxAve = 0.0 loseThresholdPer = 0.7 #search_data sql = """ SELECT nq.sales,nq.name,nq.num,to_char(dates.date,'YYYY-MM-DD') as date FROM ( (SELECT sd.sales,ss.name,sd.date,sd.num FROM summary_dailystoreitemsummary as sd, store_storeitem as ss WHERE sd.date BETWEEN to_date('%(start_date)s','YYYY-MM-DD') AND to_date('%(end_date)s','YYYY-MM-DD') AND sd.item_id = ss.id AND sd.item_id = '%(item_id)s') as nq RIGHT JOIN (SELECT generate_series('%(start_date)s', '%(end_date)s', '1 day'::interval)::date AS date) dates ON dates.date = nq.date ) ORDER BY date """ % { 'start_date': start_date, 'end_date': end_date, 'item_id': item_id } cur = connection.cursor() cur.execute(sql) result = sql_utils.dictfetchall(cur) #his_data his_start_date = (datetime.date.today() - datetime.timedelta(days=70)).strftime('%Y-%m-%d') his_end_date = datetime.date.today().strftime('%Y-%m-%d') sql = """ SELECT nq.sales,nq.name,nq.num,to_char(dates.date,'YYYY-MM-DD') as date FROM ( (SELECT sd.sales,ss.name,sd.date,sd.num FROM summary_dailystoreitemsummary as sd, store_storeitem as ss WHERE sd.date BETWEEN to_date('%(start_date)s','YYYY-MM-DD') AND to_date('%(end_date)s','YYYY-MM-DD') AND sd.item_id = ss.id AND sd.item_id = '%(item_id)s') as nq RIGHT JOIN (SELECT generate_series('%(start_date)s', '%(end_date)s', '1 day'::interval)::date AS date ) dates ON dates.date = nq.date ) ORDER BY date """ % { 'start_date': his_start_date, 'end_date': his_end_date, 'item_id': item_id } cur = connection.cursor() cur.execute(sql) his_result = sql_utils.dictfetchall(cur) his_aves = [ { 'avg': 0, 'data': [] }, { 'avg': 0, 'data': [] }, { 'avg': 0, 'data': [] }, { 'avg': 0, 'data': [] }, { 'avg': 0, 'data': [] }, { 'avg': 0, 'data': [] }, { 'avg': 0, 'data': [] }, ] for r in his_result: myDate = r['date'] sales = r['sales'] if not sales: continue week = datetime.datetime.strptime(myDate, "%Y-%m-%d").weekday() his_aves[week]['data'].append(sales) for his_ave in his_aves: his_median = 0 loseThreshold = 0 if not his_ave['data']: his_ave['median'] = his_median his_ave['loseThreshold'] = loseThreshold continue his_median = median(his_ave['data']) loseThreshold = his_median * loseThresholdPer his_ave['median'] = his_median his_ave['loseThreshold'] = loseThreshold medians = [] for week in range(7): medians.append(his_aves[week].get('median', 0)) #损失 for r in result: sales = r['sales'] if not sales: sales = 0 myDate = r['date'] week = datetime.datetime.strptime(myDate, "%Y-%m-%d").weekday() if sales < his_aves[week]['loseThreshold']: lose_dates.append(myDate) lose_count = lose_count + his_aves[week]['median'] - sales lose_count = round(lose_count, 5) lose_days = len(lose_dates) return { 'lose_days': lose_days, 'lose_dates': lose_dates, 'lose_count': lose_count, 'result': result, 'medians': medians }
def get_bagman_history(user_id, start_name, end_time): sql = """SELECT customer.customer_name, orders.order_time, to_char( orders.order_time AT TIME ZONE 'Asia/ShangHai', 'YYYY-MM-DD HH24:MI') AS order_time, suborder.status, suborder.id, item.item_name, item.unit, order_item.unit_price, order_item.num, order_item.subtotal FROM recommendorder_bagmancustomerorder AS orders LEFT JOIN recommendorder_bagmansuborder AS suborder ON orders.id=suborder.order_id LEFT JOIN recommendorder_bagmanorderitem AS order_item ON suborder.id=order_item.suborder_id LEFT JOIN recommendorder_item AS item ON order_item.item_id=item.id LEFT JOIN recommendorder_customer AS customer ON customer.id=orders.customer_id WHERE orders.operator_id=%(user_id)s AND orders.order_time>=%(start_name)s AND orders.order_time<=%(end_time)s ORDER BY suborder.id""" cur = connection.cursor() cur.execute( sql, {'user_id': user_id, 'start_name': start_name, 'end_time': end_time}) orders = sql_utils.dictfetchall(cur) temp = {} results = [] for order in orders: order_dict = {} if order['id'] in temp: order_dict = temp[order['id']] order_dict['total'] += order['subtotal'] order_dict['total_num'] += order['num'] order_dict['order_items'].append({'item': {'item_name': order['item_name'], 'unit': order['unit'], 'price': order['unit_price']}, 'num': order['num']}) else: order_dict = {'total': order['subtotal'], 'total_num': order['num'], 'status': order['status'], 'order_time': order['order_time'], 'customer_name': order['customer_name'], 'order_items': [{'item': {'item_name': order['item_name'], 'unit': order['unit'], 'price': order['unit_price']}, 'num': order['num']}], } results.append(order_dict) temp[order['id']] = order_dict results = sorted( results, key=lambda dict: dict["order_time"], reverse=True) return results
def search_dealer_customers(form, user): name = form.cleaned_data.get('name') salesman = user.salesman dealer = salesman.dealer sql = ''' SELECT c.id FROM recommendorder_customerorder AS a LEFT JOIN recommendorder_dealercustomermap AS b ON a.customer_id=b.customer_id LEFT JOIN recommendorder_customer AS c ON b.customer_id=c.id WHERE b.dealer_id=%(dealer_id)s AND a.order_time<=now() AND a.order_time>=date_trunc('day', now()); ''' cur = connection.cursor() cur.execute(sql, {'dealer_id': dealer.id}) record_results = sql_utils.dictfetchall(cur) sql = ''' SELECT c.id FROM recommendorder_bagmancustomerorder AS a LEFT JOIN recommendorder_dealercustomermap AS b ON a.customer_id=b.customer_id LEFT JOIN recommendorder_customer AS c ON b.customer_id=c.id WHERE b.dealer_id=%(dealer_id)s AND a.order_time<=now() AND a.order_time>=date_trunc('day', now()); ''' cur = connection.cursor() cur.execute(sql, {'dealer_id': dealer.id}) bagman_orders = sql_utils.dictfetchall(cur) record_dict = {} for record in record_results: record_dict[record['id']] = 1 for order in bagman_orders: record_dict[order['id']] = 1 sql = ''' SELECT b.* FROM recommendorder_dealercustomermap AS a LEFT JOIN recommendorder_customer AS b ON a.customer_id=b.id WHERE a.dealer_id=%(dealer_id)s AND b.customer_name LIKE %(name)s; ''' cur = connection.cursor() cur.execute(sql, {'dealer_id': dealer.id, 'name': '%' + name + '%'}) records = sql_utils.dictfetchall(cur) search_results = [] now = timezone.now().timetuple() day = now.tm_mday year = now.tm_year month = now.tm_mon for record in records: if record['lat'] and record['lng']: record['lat'] = float(record['lat']) record['lng'] = float(record['lng']) if record_dict.get(record['id']) == 1: record['is_buy'] = 1 else: record['is_buy'] = 0 search_results.append(record) return search_results
def get_work_checkin(brand_id, type, start_time, end_time): datelist = pandas.date_range(start_time, end_time) results = [] for date in datelist: date = str(date)[:10] sql = ''' SELECT guide.id, guide.name as guide, store.store_name as store, guide.telephone, (checkin.work_time AT TIME ZONE 'CCT') as work_time, (checkin.worked_time AT TIME ZONE 'CCT') as worked_time, (realtime_checkin.time AT TIME ZONE 'CCT') as realtime_checkin_time, checkin.phone_model as phone, work.work_type, statement.operator_id, guide.status FROM dailystatement_guide as guide LEFT JOIN dailystatement_store as store ON guide.store_id = store.id LEFT JOIN ( SELECT guide_id, min(work_time) as work_time, max(worked_time) as worked_time, min(phone_model) as phone_model FROM dailystatement_checkin WHERE (work_time AT TIME ZONE 'CCT')::date = '{date}' GROUP BY guide_id ) AS checkin ON guide.id = checkin.guide_id LEFT JOIN ( SELECT guide_id, max(time) as time FROM dailystatement_realtimecheck WHERE (time AT TIME ZONE 'CCT')::date = '{date}' GROUP BY guide_id ) AS realtime_checkin ON guide.id = realtime_checkin.guide_id LEFT JOIN ( SELECT operator_id FROM dailystatement_statement WHERE (create_time AT TIME ZONE 'CCT')::date = '{date}' GROUP BY operator_id ) AS statement ON guide.id = statement.operator_id LEFT JOIN ( SELECT guide_id, work_type FROM dailystatement_guideworkplan WHERE date = '{date}' ) as work ON guide.id = work.guide_id WHERE guide.status = {type} AND guide.brand_id = {brand_id} ORDER BY work_time DESC; '''.format(date=date, type=type, brand_id=brand_id) cur = connection.cursor() cur.execute(sql) data = sql_utils.dictfetchall(cur) for result in data: result['date'] = date results.extend(data) return results
def get_salesman_all_customers(dealer_id): sql = '''SELECT b.customer_id, b.customer_name, b.customer_address, b.id, a.dealer_id FROM recommendorder_dealercustomermap AS a LEFT JOIN recommendorder_customer AS b ON a.customer_id=b.id LEFT JOIN recommendorder_customerorder AS c ON b.id=c.customer_id WHERE a.dealer_id= %(dealer_id)s AND b.deleted_flag is FALSE AND a.status = 1 GROUP BY b.id, a.dealer_id, b.customer_id, b.customer_name, b.customer_address ORDER BY count(a.customer_id) DESC LIMIT 100; ''' cur = connection.cursor() cur.execute(sql, {'dealer_id': dealer_id}) results = sql_utils.dictfetchall(cur) sql = ''' SELECT c.id FROM recommendorder_customerorder AS a LEFT JOIN recommendorder_dealercustomermap AS b ON a.customer_id=b.customer_id LEFT JOIN recommendorder_customer AS c ON b.customer_id=c.id WHERE b.dealer_id=%(dealer_id)s AND a.order_time<=now() AND a.order_time>=date_trunc('day', now()); ''' cur = connection.cursor() cur.execute(sql, {'dealer_id': dealer_id}) record_results = sql_utils.dictfetchall(cur) sql = ''' SELECT c.id FROM recommendorder_bagmancustomerorder AS a LEFT JOIN recommendorder_dealercustomermap AS b ON a.customer_id=b.customer_id LEFT JOIN recommendorder_customer AS c ON b.customer_id=c.id WHERE b.dealer_id=%(dealer_id)s AND a.order_time<=now() AND a.order_time>=date_trunc('day', now()); ''' cur = connection.cursor() cur.execute(sql, {'dealer_id': dealer_id}) bagman_orders = sql_utils.dictfetchall(cur) customers = [] record_dict = {} for record in record_results: record_dict[record['id']] = 1 for order in bagman_orders: record_dict[order['id']] = 1 cur.close() for result in results: is_buy = 0 if record_dict.get(result['id']) == 1: is_buy = 1 result['is_buy'] = is_buy customers.append(result) return customers