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, '商品订单更新')