def get(self, request, *args, **kwargs):
now = datetime.datetime.now()
year = now.year
total_cost = \
session.query(func.sum(Excel.total_cost_aa)).filter(
Excel.belong_id == self.site.id, Excel.year == year).first()[0]
oil_gross_p = \
session.query(func.sum(Excel.oil_gross_profit_w)).filter(Excel.belong_id == self.site.id,
Excel.year == year).first()[
0]
oil_amount = \
session.query(func.sum(Excel.oil_amount_n)).filter(Excel.belong_id == self.site.id,
Excel.year == year).first()[
0]
return self.render_to_response({
'balance':
round(total_cost / (oil_gross_p / oil_amount)),
'api_name':
self.api_name,
'advice':
self.site.advice,
'cost_control':
self.site.cost_control,
'promote':
self.site.promote
})
def query_by_pagination(site,
session,
obj,
total,
order_by='id',
start_offset=0,
end_offset=0,
limit=1000,
name='default'):
total_page = int(math.ceil(total / float(limit)))
start = 0
if start_offset:
start = start_offset / limit
if end_offset:
total_page = end_offset / limit
for i in xrange(start, total_page):
offset = limit * i
result = session.query(obj).filter(
obj.catch_payment == False, obj.belong_id == site.id).order_by(
order_by).limit(limit).offset(offset).all()
logging.info('Thread {0} {1}: Current {2}->{3}/{4} {5}%'.format(
name, site.slug, offset, offset + limit, total,
float(offset + limit) / total * 100))
yield result
def get_payment(thread_name, obj, start_offset=0, end_offset=0, limit=100):
for orders in query_by_pagination(site,
session,
obj,
total,
start_offset=start_offset,
end_offset=end_offset,
limit=limit,
name=thread_name):
ib_session = init_interbase_connect(site.fuel_server)
tills = ','.join([unicode(i.till_id) for i in orders])
if not tills:
return
sql = '''select TILLITEM_PMNT_SPLIT.TILLNUM, TILLITEM_PMNT_SPLIT.PMSUBCODE, PMNT.PMNT_NAME from TILLITEM_PMNT_SPLIT,
PMNT where TILLITEM_PMNT_SPLIT.TILLNUM IN ({0}) AND
PMNT.PMSUBCODE_ID=TILLITEM_PMNT_SPLIT.PMSUBCODE'''.format(tills)
ib_session.execute(sql)
res = ib_session.fetchall()
for itm in res:
till_id, payment_code, payment_type = itm
order = session.query(FuelOrder).filter(
FuelOrder.till_id == till_id).first()
if order:
order.payment_code = payment_code
order.payment_type = get_clean_data(payment_type)
order.catch_payment = True
try:
session.commit()
logging.info('INFO commit to db success site {0}'.format(
site.name))
except Exception as e:
logging.exception(
'ERROR in commit session site {0} reason {1}'.format(
site.name, e))
session.rollback()
def get_from_db(self, belong, group_condition, fmt_str):
res = session.query(
group_condition,
func.sum(self.model.total_profit_f) /
func.sum(self.model.oil_amount_n)).filter(
self.model.belong_id == belong).group_by(group_condition)
return res
def get_fuel_order_by_hash(hash_str):
try:
obj = session.query(FuelOrder).filter(
FuelOrder.hash == hash_str).first()
return obj
except Exception as e:
print e
return None
def get_obj_by_hash(hash_str, obj_class):
try:
obj = session.query(obj_class).filter(
obj_class.hash == hash_str).first()
return obj
except Exception as e:
print e
return None
def get_original_ys_data(self, context, st, et):
if not self.ys_column:
return None
fmt_str, fmt = self.get_time_fmt(st, et)
ys = getattr(self.site, self.ys_column)
if fmt_str == 'month':
res = session.query(Excel.month).filter(
Excel.belong_id == self.site.id,
self.model.year == context['year']).all()
ys /= 12
else:
res = session.query(
Excel.year).filter(Excel.belong_id == self.site.id).all()
res_dict = {}
for itm in res:
res_dict[itm[0]] = round(ys)
return res_dict
def check_site():
site_list = session.query(Site).filter(Site.check == False).all()
for site in site_list:
if check_fuel(site.fuel_server) and check_eps(site.bos_server):
logging.info('check site {0} success'.format(site.name))
site.check = True
else:
logging.info('check site {0} failed'.format(site.name))
session.commit()
def query_by_pagination(session, obj, order_by='id', start_offset=0, limit=1000):
def get_count(q):
count_q = q.statement.with_only_columns([func.count()]).order_by(None)
count = q.session.execute(count_q).scalar()
return count
total = get_count(session.query(obj))
total_page = int(math.ceil(total / float(limit)))
start = 0
if start_offset:
start = start_offset / limit
for i in xrange(start, total_page):
offset = limit * i
result = session.query(obj).filter(obj.catch_payment == False).order_by(order_by).limit(limit).offset(
offset).all()
logging.info(
'Current {0}->{1}/{2} {3}%'.format(offset, offset + limit, total, float(offset + limit) / total * 100))
yield result
def get_original_time_data(self, context, st, et, belong=None):
fmt_str, fmt = self.get_time_fmt(st, et)
belong = self.site.id if not belong else belong
group_condition = self.model.month if fmt_str == 'month' else self.model.year
res = session.query(
group_condition,
func.sum(Excel.total_cost_aa - Excel.water_ele_cost_af -
Excel.daily_repair_ad - Excel.salary_cost_ab -
Excel.depreciation_cost_ao - Excel.oil_loss_ai)
).filter(self.model.belong_id == belong).group_by(group_condition)
m = 1
if fmt_str == 'month':
res = res.filter(self.model.year == st.year).all()
else:
m = session.query(
Excel.month).filter(Excel.belong_id == self.site.id).order_by(
Excel.year.desc(), Excel.month.desc()).first()[0]
res = res.filter(self.model.month <= m).all()
res_dict = {}
for itm in res:
res_dict[itm[0]] = round(itm[1])
return res_dict
def get_original_time_data(self, context, st, et, belong=None):
fmt_str, fmt = self.get_time_fmt(st, et)
belong = self.site.id if not belong else belong
group_condition = fmt
res = session.query(group_condition,
func.sum(self.model.total)).filter(
self.model.original_create_time.between(
st, et), self.model.belong_id ==
belong).group_by(group_condition).all()
res_dict = {}
for itm in res:
res_dict[itm[0]] = itm[1]
return res_dict
def init_in_test(slug, task_id=None):
import time
try:
begin = datetime.datetime.now()
now = get_now_time_with_timezone()
logging.info('开始初始化任务 {0: %Y-%m-%d %H:%M:%S}'.format(begin))
site = session.query(Site).filter(Site.slug == slug).first()
if site:
task = Task()
task.task_id = task_id
task.belong_id = site.id
task.name = '初始化任务: {0}'.format(site.name)
task.create_time = now
task.original_create_time = now
task.modify_time = now
session.add(task)
try:
session.commit()
except Exception as e:
logging.exception(
'ERROR in commit session site {0} reason {1}'.format(
slug, e))
session.rollback()
init_st = add_timezone_to_naive_time(
datetime.datetime(2016, 12, 15))
init_et = add_timezone_to_naive_time(datetime.datetime.now())
total_days = (init_et - init_st).days
times = total_days / 7
detla = datetime.timedelta(days=7)
count = 0.0
et = init_st
while 1:
st = et
et = et + detla
count += 1
percent = round(count / times, 2) * 100
update_init_progress(task_id, percent, '任务执行中')
time.sleep(0.5)
if st > init_et:
break
end = datetime.datetime.now()
msg = '结束初始化任务 {0: %Y-%m-%d %H:%M:%S} 共计耗时 {1} 分钟'.format(
end, (end - begin).seconds / 60.0)
update_init_progress(task_id, 100, msg, TaskStatus.finish)
except Exception as e:
logging.exception('ERROR in init all site {0} reason {1}'.format(
slug, e))
session.rollback()
update_init_progress(task_id, 0, e.message, TaskStatus.error)
def get_original_time_data(self, context, st, et, belong=None):
fmt_str, fmt = self.get_time_fmt(st, et)
belong = self.site.id if not belong else belong
group_condition = fmt
res = session.query(group_condition,
func.sum(self.model.amount)).filter(
self.model.fuel_type == self.fuel_dict.get(
context.get('fuel_type')).encode('utf-8'),
self.model.original_create_time.between(
st, et), self.model.belong_id ==
belong).group_by(group_condition).all()
res_dict = {}
for itm in res:
res_dict[itm[0]] = itm[1] * self.get_str_dens(
context.get('fuel_type')) / 1000.0
return res_dict
def abnormal_card_check(card_id):
if not card_id:
return 0, ''
st, et = get_today_st_et()
card_sql = session.query(CardRecord).filter(
CardRecord.card_id == unicode(card_id))
day_card_res = card_sql.filter(
CardRecord.original_create_time.between(st, et))
# 日异常判断:
count = day_card_res.count()
if count > 3:
return 1, '单日刷卡三次以上'
day = day_card_res.filter(CardRecord.total >= 80000,
CardRecord.classification == '汽油')
count = day.count()
if count > 0:
return 1, '单笔汽油消费 800 元以上'
net, nst = get_today_night()
day_card_res = day_card_res.filter(
CardRecord.original_create_time.between(st, net),
CardRecord.original_create_time.between(nst, et))
count = day_card_res.count()
if count >= 3:
return 1, '单日深夜刷卡三次及以上'
# 周异常判断:
st, et = get_week_st_et()
week_card_res = card_sql.filter(
CardRecord.original_create_time.between(st, et))
week = week_card_res.filter(CardRecord.total >= 200000,
CardRecord.classification == '汽油')
count = week.count()
if count > 0:
return 2, '一周内汽油消费2000元及以上'
week = week_card_res.filter(CardRecord.total >= 1000000,
CardRecord.classification == '柴油')
count = week.count()
if count > 0:
return 2, '一周内柴油消费10000元及以上'
# mix = session.query(CardRecord.classification, func.count(1)).filter(
# CardRecord.classification.in_(('汽油', '柴油')),
# CardRecord.original_create_time.between(st, et)).group_by(
# CardRecord.classification).all()
# if len(mix) < 2:
# return 0, ''
# if mix[0][1] > 0 and mix[1][1] > 0:
# return 2, '一周内汽柴油混刷'
return 0, ''
def update_rev(rid, site, **kwargs):
res = session.query(Receiver).filter(
Receiver.rid == rid, Receiver.belong_id == site.id).first()
if not res:
res = Receiver()
res.rid = rid
res.belong_id = site.id
for k, v in kwargs.items():
setattr(res, k, v)
session.add(res)
try:
session.commit()
except Exception as e:
logging.exception('ERROR in commit session site {0} reason {1}'.format(
kwargs.get('belong_id'), e))
session.rollback()
return res
def update_second_classification(cid, **kwargs):
res = session.query(SecondClassification).filter(
SecondClassification.id == cid).first()
if not res:
res = SecondClassification()
res.create_time = get_now_time_with_timezone()
res.id = cid
for k, v in kwargs.items():
setattr(res, k, v)
res.original_create_time = get_now_time_with_timezone()
session.add(res)
try:
session.commit()
except Exception as e:
logging.exception('ERROR in commit session site {0} reason {1}'.format(
kwargs.get('belong_id'), e))
session.rollback()
return res
def get_original_time_data(self, context, st, et, belong=None):
fmt_str, fmt = self.get_time_fmt(st, et)
belong = self.site.id if not belong else belong
group_condition = self.model.month if fmt_str == 'month' else self.model.year
res = self.get_from_db(belong, group_condition, fmt_str)
m = 1
if fmt_str == 'month':
res = res.filter(self.model.year == st.year).all()
else:
m = session.query(
Excel.month).filter(Excel.belong_id == self.site.id).order_by(
Excel.year.desc(), Excel.month.desc()).first()[0]
res = res.filter(self.model.month <= m).all()
res_dict = {}
for itm in res:
if fmt_str == 'year':
res_dict[itm[0]] = round(itm[1] / self.site.open_time)
else:
res_dict[itm[0]] = round(itm[1] / 31)
return res_dict
def init_periodic_tasks(sender, **kwargs):
site_list = [
itm[0]
for itm in session.query(Site.slug).filter(Site.check == True).all()
]
# site_list = ['mf', '54', 'air']
for site in site_list:
sender.add_periodic_task(
datetime.timedelta(minutes=1),
# crontab(hour=7, minute=30, day_of_week=1),
get_tank_info_task.s(site),
)
sender.add_periodic_task(
datetime.timedelta(minutes=5),
get_inventory_record_task.s(site),
)
sender.add_periodic_task(
datetime.timedelta(seconds=70),
get_fuel_order_task.s(site),
)
sender.add_periodic_task(
datetime.timedelta(minutes=1),
get_store_order_task.s(site),
)
sender.add_periodic_task(
datetime.timedelta(minutes=1),
get_delivery_task.s(site),
)
sender.add_periodic_task(
crontab(hour=4, minute=30),
get_inventories_task.s(site),
)
sender.add_periodic_task(
datetime.timedelta(seconds=50),
get_card_record_task.s(site),
)
sender.add_periodic_task(
crontab(hour=4, minute=10, day_of_month=1),
# datetime.timedelta(minutes=10),
init_task.s(site),
)
def get_object_by_id(oid, site, obj_class):
obj = session.query(obj_class).filter(
obj_class.id == oid, obj_class.belong_id == site.id).first()
return obj
def get_fuel_order_payment(site, threads=2, today=False):
t_orders = session.query(FuelOrder).filter(
FuelOrder.catch_payment == False, FuelOrder.belong_id == site.id)
if today:
t_orders = t_orders.filter(
FuelOrder.create_time == datetime.datetime.now().date())
t_orders = t_orders.all()
total = len(t_orders)
thread_nums = threads
thread_list = []
slice_num = total / thread_nums
offset = 0
def get_payment(thread_name, obj, start_offset=0, end_offset=0, limit=100):
for orders in query_by_pagination(site,
session,
obj,
total,
start_offset=start_offset,
end_offset=end_offset,
limit=limit,
name=thread_name):
ib_session = init_interbase_connect(site.fuel_server)
tills = ','.join([unicode(i.till_id) for i in orders])
if not tills:
return
sql = '''select TILLITEM_PMNT_SPLIT.TILLNUM, TILLITEM_PMNT_SPLIT.PMSUBCODE, PMNT.PMNT_NAME from TILLITEM_PMNT_SPLIT,
PMNT where TILLITEM_PMNT_SPLIT.TILLNUM IN ({0}) AND
PMNT.PMSUBCODE_ID=TILLITEM_PMNT_SPLIT.PMSUBCODE'''.format(tills)
ib_session.execute(sql)
res = ib_session.fetchall()
for itm in res:
till_id, payment_code, payment_type = itm
order = session.query(FuelOrder).filter(
FuelOrder.till_id == till_id).first()
if order:
order.payment_code = payment_code
order.payment_type = get_clean_data(payment_type)
order.catch_payment = True
try:
session.commit()
logging.info('INFO commit to db success site {0}'.format(
site.name))
except Exception as e:
logging.exception(
'ERROR in commit session site {0} reason {1}'.format(
site.name, e))
session.rollback()
for i in range(thread_nums):
if i == thread_nums + 1:
e_offset = total
else:
e_offset = offset + slice_num
t = threading.Thread(target=get_payment,
args=(i, FuelOrder, offset, e_offset, 100))
offset += slice_num
thread_list.append(t)
for t in thread_list:
t.start()
for t in thread_list:
t.join()
def get_latest_settlement_record(tank_id):
obj = session.query(InventoryRecord).filter(
InventoryRecord.record_type == 3,
InventoryRecord.tank_id == tank_id).order_by(
InventoryRecord.original_create_time.desc()).first()
return obj
def check_card_record(unique_id):
res = session.query(CardRecord).filter(
CardRecord.parent_id == unique_id).all()
return True if res else False
def get_record_by_hash(hash_str):
obj = session.query(InventoryRecord).filter(
InventoryRecord.hash == hash_str).first()
return obj
def get_tank_by_tank_id(tid, site_id, *args, **kwargs):
obj = session.query(FuelTank).filter(
FuelTank.tank_id == tid, FuelTank.belong_id == site_id).first()
if not obj:
obj = create_tank(tid, site_id, *args, **kwargs)
return obj
def get_site_by_slug(slug):
obj = session.query(Site).filter(Site.slug == slug).first()
return obj if obj else None
def get_sup_by_sid(sid, site):
obj = session.query(Supplier).filter(
Supplier.sid == sid, Supplier.belong_id == site.id).first()
return obj
def get_second_cls_by_id(cid):
res = session.query(SecondClassification).filter(
SecondClassification.id == cid).first()
return res
def get_goods_inventory_by_barcode(barcode, site):
obj = session.query(GoodsInventory).filter(
GoodsInventory.barcode == barcode,
GoodsInventory.belong_id == site.id).first()
return obj
def get_all_tanks_by_site(site):
objs = session.query(FuelTank).filter(FuelTank.belong_id == site.id).all()
return objs
def get_rev_by_rid(rid, site):
obj = session.query(Receiver).filter(
Receiver.rid == rid, Receiver.belong_id == site.id).first()
return obj
