def get_inventories(site):
site = get_site_by_slug(site)
ib_session = init_interbase_connect(site.fuel_server)
sql = '''SELECT SUPERDEPT.SUPERDEPTID,
DEPT.DEPTID,
SUBDEPT.SUBDEPTID,
ITEM.BARCODE,
ITEM.FULLDESCRIPTION,
ITEM.ITEMCODE,
UNITSIZE.UNITNAME,
ITEM.ITEMID,
SUM(ITEMLOCTOTAL.CALCBALANCE_QTY) as total
FROM ITEMLOCTOTAL,ITEM,SUBDEPT,DEPT,SUPERDEPT,UNITSIZE
WHERE
(UNITSIZE.UNITID=ITEM.MANAGEUNIT)
AND
(SUBDEPT.SUBDEPTID=ITEM.DEPT)
AND
(SUBDEPT.DEPTID=DEPT.DEPTID)
AND
(DEPT.SUPERDEPTID=SUPERDEPT.SUPERDEPTID)
AND
(ITEMLOCTOTAL.ITEM_ID = ITEM.ITEMID)
GROUP BY
SUPERDEPT.SUPERDEPTID,DEPT.DEPTID,SUBDEPT.SUBDEPTID,ITEM.FULLDESCRIPTION,ITEM.BARCODE,
ITEM.ITEMCODE,UNITSIZE.UNITNAME,ITEM.MANAGUNITFACTOR,ITEM.ITEMID
ORDER BY
ITEM.ITEMCODE'''
ib_session.execute(sql)
res = ib_session.fetchall()
total = len(res)
for itm in res:
f_cls, s_cls, t_cls, barcode, name, itemcode, unit, itemid, amount = itm
barcode = get_clean_data(barcode)
name = get_clean_data(name)
unit = get_clean_data(unit)
unique_str = generate_hash(unicode(f_cls), unicode(s_cls),
unicode(t_cls), barcode, unicode(site.id))
update_goods_inventory(unique_str,
name=name,
unit=unit,
hash=unique_str,
barcode=barcode,
itemcode=barcode,
third_cls_id=t_cls,
py=get_py(name),
second_cls_id=s_cls,
cls_id=f_cls,
amount=amount,
belong_id=site.id)
logging.info('SUCCESS update goods inventory total {0}'.format(total))
update_site_status(site, '商品库存更新')
def get_fuel_order(site, start_time=None, end_time=None):
if not start_time:
start_time = datetime.datetime.now() - datetime.timedelta(hours=3)
end_time = start_time + datetime.timedelta(days=1)
st = datetime_to_string(start_time)
et = datetime_to_string(end_time)
site = get_site_by_slug(site)
ib_session = init_interbase_connect(site.fuel_server)
sql = '''select tillnum,timeopen,FULLDESCRIPTION,price,weight,total, tillitem.PUMP_ID, ITEM.DEPT, ITEM.BARCODE from till left
outer join tillitem on (till.tillnum=tillitem.tillnum) and
(till.pos_batch_id=tillitem.pos_batch_id),item,fuel_pumps_hose where tillitem.grade
=item.grade and tillitem.pump_id=fuel_pumps_hose.pump_id and
tillitem.hose_id=fuel_pumps_hose.hose_id and tillitem.STATUSTYPE =1 and timeopen between
'{0}' AND '{1}' and grade>0 order by
virtual_hose_id,timeopen DESC'''.format(st, et)
ib_session.execute(sql)
orders = ib_session.fetchall()
nums = 0
for order in orders:
till_id, original_create_time, fuel_type, price, amount, total_price, pump_id, dept, barcode = order
fuel_type = get_clean_data(fuel_type)
barcode = get_clean_data(barcode)
super_dept = unicode(dept)[:6]
unique_str = generate_hash(
unicode(till_id),
datetime_to_string(original_create_time, '%Y-%m-%d %H:%M:%S'),
unicode(price), unicode(amount), unicode(pump_id),
unicode(total_price))
res = get_fuel_order_by_hash(unique_str)
if res:
continue
create_fuel_order(till_id=till_id,
original_create_time=original_create_time,
fuel_type=fuel_type,
price=price,
total_price=total_price,
amount=amount,
pump_id=pump_id,
hash=unique_str,
belong_id=site.id,
classification_id=dept,
barcode=barcode,
super_cls_id=int(super_dept))
nums += 1
logging.info(
'=============create fuel order {0} site {1}=============='.format(
nums, site.name))
# get_fuel_order_payment(site)
update_site_status(site, '油品订单更新')
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_delivery(site, start_time=None, end_time=None):
if not start_time:
start_time = datetime.datetime.now() - datetime.timedelta(hours=3)
end_time = start_time + datetime.timedelta(days=1)
st = datetime_to_string(start_time)
et = datetime_to_string(end_time)
site = get_site_by_slug(site)
ib_session = init_interbase_connect(site.fuel_server)
sql = '''Select EXTREF, pickup_date, ITEMDOCTYPE_ID, SUPPLIER_ID, TRUCK_NUMBER
From Fuel_Tank_Delivery_Header
WHERE DELIVERY_DATE BETWEEN '{0}' and '{1}'
Order By EXTREF'''.format(st, et)
ib_session.execute(sql)
res = ib_session.fetchall()
for itm in res:
_, original_create_time, rev_id, sup_id, number = itm
number = get_clean_data(number)
unique_str = generate_hash(
_, datetime_to_string(original_create_time, '%Y-%m-%d %H:%M:%S'),
unicode(rev_id), unicode(sup_id), number[2:])
obj = get_obj_by_hash(unique_str, DeliveryRecord)
if obj:
continue
rec = get_rev_by_rid(rev_id, site)
sup = get_sup_by_sid(sup_id, site)
if rec and sup:
create_object(DeliveryRecord,
supplier=sup.name,
receiver=rec.name,
truck_number=number,
belong_id=site.id,
original_create_time=original_create_time,
hash=unique_str,
modify_time=original_create_time)
update_site_status(site, '油品配送记录更新')
def get_tank_grade(site):
site = get_site_by_slug(site)
tanks = get_all_tanks_by_site(site)
ib_session = init_interbase_connect(site.fuel_server)
for tank in tanks:
if not tank.grade_id:
continue
sql = 'SELECT GRADE, GRADENAME FROM FUELGRADE WHERE GRADE={0}'.format(
tank.grade_id)
ib_session.execute(sql)
res = ib_session.fetchone()
if not res:
continue
grade, grade_name = res
tank.name = get_clean_data(grade_name)
logging.info(
'INFO read tank fuel type for site {0} tank {1} success, new fuel: {2}'
.format(site.name, tank.tank_id, tank.name))
try:
session.commit()
except Exception as e:
logging.exception('ERROR in commit session site {0} reason {1}'.format(
site.name, e))
session.rollback()
update_site_status(site, '油库种类更新成功')
def get_rev(site):
site = get_site_by_slug(site)
ib_session = init_interbase_connect(site.fuel_server)
sql = '''SELECT ITEMDOCTYPE_ID ,ITEMDOCTYPE_NAME FROM ITEMDOCTYPE'''
ib_session.execute(sql)
res = ib_session.fetchall()
for itm in res:
rid, name = itm
name = get_clean_data(name)
update_rev(rid, site, name=name)
def get_sup(site):
site = get_site_by_slug(site)
ib_session = init_interbase_connect(site.fuel_server)
sql = '''SELECT SUPPLIERID ,SUPPLIERNAME FROM SUPPLIER'''
ib_session.execute(sql)
res = ib_session.fetchall()
for itm in res:
sid, name = itm
name = get_clean_data(name)
update_sup(sid, site, name=name)
def get_second_classify(site):
site = get_site_by_slug(site)
ib_session = init_interbase_connect(site.fuel_server)
sql = '''select DEPTID, DEPTNAME, SUPERDEPTID from DEPT'''
ib_session.execute(sql)
res = ib_session.fetchall()
for itm in res:
cid, name, parent_id = itm
name = get_clean_data(name)
update_second_classification(cid, name=name, parent_id=parent_id)
def get_first_classify(site):
site = get_site_by_slug(site)
ib_session = init_interbase_connect(site.fuel_server)
sql = '''select SUPERDEPTID, SUPERDEPTNAME from SUPERDEPT'''
ib_session.execute(sql)
res = ib_session.fetchall()
for itm in res:
cid, name = itm
try:
name = get_clean_data(name)
update_classification(cid, name=name)
except Exception as e:
logging.exception(
'ERROR get first classify in {0} reason {1}'.format(cid, e))
continue
def get_store_order(site, start_time=None, end_time=None):
if not start_time:
start_time = datetime.datetime.now() - datetime.timedelta(hours=3)
end_time = start_time + datetime.timedelta(days=1)
st = datetime_to_string(start_time)
et = datetime_to_string(end_time)
site = get_site_by_slug(site)
ib_session = init_interbase_connect(site.fuel_server)
sql = '''SELECT
TILL.SALEDATE,
POSBATCH.POS_ID,
TILL.TILLNUM,
TILL.SHIFT,
TILL.TIMECLOSE,
ITEM.DEPT,
ITEM.BARCODE,
ITEM.FULLDESCRIPTION AS ITEMNAME,
UNITSIZE.UNITNAME,
TILLITEM.STDPRICE PRICE,
(TILLITEM.TOTAL) AS TOTAL,
(TILLITEM.QTY * TILLITEM.WEIGHT) AS QTY,
(TILLITEM.QTY * TILLITEM.WEIGHT * TILLITEM.STDPRICE) AS CALC_TOTAL
FROM DAYBATCH DAYBATCH , POSBATCH, TILLITEM,TILL, ITEM ,UNITSIZE
WHERE
( TILLITEM.STATUSTYPE NOT IN ( 26 )) AND
( TILL.STATUSTYPE NOT IN ( 26 )) AND
(DAYBATCH.DAY_BATCH_DATE BETWEEN '{0}' AND '{1}')
AND ( TILLITEM.TILLNUM = TILL.TILLNUM)
AND ( TILLITEM.POS_BATCH_ID = TILL.POS_BATCH_ID)
AND ( TILL.POS_BATCH_ID = POSBATCH.POS_BATCH_ID)
AND ( POSBATCH.DAY_BATCH_ID = DAYBATCH.DAY_BATCH_ID)
AND ( TILLITEM.PLU = ITEM.ITEMID )
AND ( UNITSIZE.UNITID = ITEM.BASEUNIT)
AND ( TILL.STATUSTYPE IN (1,2,3,4,15))
AND ( TILLITEM.STATUSTYPE IN (1,2,3,4,15))
ORDER BY
TILL.TIMECLOSE DESC'''.format(st, et)
ib_session.execute(sql)
orders = ib_session.fetchall()
nums = 0
for order in orders:
sale_date, pos_id, till_id, shift, original_create_time, dept, barcode, name, unit, price, total, amount, _ = order
dept_str = unicode(dept)
if dept_str.startswith('1001'):
continue
name = get_clean_data(name)
barcode = get_clean_data(barcode)
unique_str = generate_hash(
unicode(till_id),
datetime_to_string(original_create_time, '%Y-%m-%d %H:%M:%S'),
unicode(price), unicode(amount), unicode(barcode), unicode(total),
unicode(site.id))
res = get_goods_order_by_hash(unique_str)
if res:
continue
create_goods_order(till_id=till_id,
original_create_time=original_create_time,
classification_id=dept,
price=price,
total=total,
amount=amount,
barcode=barcode,
hash=unique_str,
name=name,
belong_id=site.id,
super_cls_id=int(dept_str[:4]))
nums += 1
gi = get_goods_inventory_by_barcode(barcode, site)
if gi:
gi.last_sell_time = add_timezone_to_naive_time(
original_create_time)
try:
session.commit()
except Exception as e:
logging.exception(
'ERROR in commit session site {0} reason {1}'.format(
site.name, e))
session.rollback()
logging.info(
'=============create store order {0} site {1}=============='.format(
nums, site.name))
# get_goods_order_payment(site)
update_site_status(site, '商品订单更新')