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
