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_card_record(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) # ms_session = init_test() ms_session = init_mysql_connect(site.bos_server) sql = '''select id, balance, details, nozzle, cardasn, bankcard, cardType, uniqueid, recordtime from tbl_epstrade where status in (2,3,5,23) and tradeType in (10,11,13,30,21) and recordtime BETWEEN '{0}' and '{1}' order by recordtime desc'''.format(st, et) res = ms_session.execute(sql).fetchall() nums = 0 for itm in res: unique_id, balance, details, pump_id, card_id, bank_card_id, card_type, eps_unique_id, original_create_time = itm unique_str = generate_hash( unicode(pump_id), datetime_to_string(original_create_time, '%Y-%m-%d %H:%M:%S'), unicode(card_id), unicode(bank_card_id), unicode(eps_unique_id), unicode(unique_id), unicode(site.id)) exist = check_card_record(unique_str) if exist: continue detail_list = details_handle(details) for index, dt in enumerate(detail_list): cls, detail, total = dt create_card_record(unique_id='{0}{1}'.format(unique_str, index), parent_id=unique_str, card_id=card_id, bank_card_id=bank_card_id, detail=detail, pump_id=pump_id, balance=balance, total=total, card_type=card_type, classification=cls, eps_unique_id=eps_unique_id, belong_id=site.id, original_create_time=original_create_time) nums += 1 if card_type in (1, 2): abnormal_type, reason = abnormal_card_check(card_id) if abnormal_type: create_abnormal_record(abnormal_type, card_id=card_id, card_type=card_type, reason=reason, belong_id=site.id) logging.info( '=============create card record {0} site {1}=============='.format( nums, site.name)) update_site_status(site, '卡信息更新')
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 post(self, request, *args, **kwargs): # try: # from PIL import Image img_data = request.FILES.get('image') # img = Image.open(img_data) name = generate_hash(img_data.name, unicode(time.time())) from django.core.files.storage import default_storage from django.core.files.base import ContentFile default_storage.save('{0}image/{1}.png'.format(STATIC_ROOT, name), ContentFile(img_data.read())) # f = open('{0}image/{1}.png'.format(STATIC_ROOT, name), "wb") # f.write(img_data) # f.close() # img.save('{0}image/{1}.png'.formaat(STATIC_ROOT, name), "PNG") return self.render_to_response( {'url': '/static/image/{0}.png'.format(name)})
def get_inventory_record(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) sql = '''SELECT FTR.TANK_ID, FS.SHIFT_END ACTIVITY_TIME, 3 ACTIVITY_TYPE, CAST(NULL AS VARCHAR(20)) EXTREF, CAST(NULL AS DOUBLE PRECISION) ORIGINAL_VOLUME, CAST(NULL AS DOUBLE PRECISION) RECEIVING_VOLUME, FTR.GROSS_STICK, FTR.WATER_STICK, FTR.CLOSE_QTY FUEL_VOLUME, FTR.TANK_TEMP FUEL_TEMP, FTR.DENSITY FUEL_DENSITY, FTR.DENSITY_15 FUEL_DENSITY_STD, FTR.CLOSE_VOLUME_15 FUEL_VOLUME_STD, FTR.SHIFT_CONTROL_ID, FS.SHIFT_START SHIFT_START, FTR.MANUAL_TEMPERATURE_1 AS ENV_TEMP FROM FUEL_SHIFT_CONTROL FS, DAYBATCH DB, FUEL_TANK_READING FTR WHERE FTR.SHIFT_CONTROL_ID = FS.SHIFT_CONTROL_ID AND FS.DAY_BATCH_ID = DB.DAY_BATCH_ID AND DB.DAY_BATCH_DATE BETWEEN '{start_time}' AND '{end_time}' AND NOT (FS.SHIFT_END IS NULL) UNION /* 2 - readings on end delivery */ SELECT FTD.TANK_ID, FTD.DELIVERY_END_TIME, 2, FTH.EXTREF, COMPAREINT(FTH.ITEMDOCTYPE_ID, 11, '=') * (- FTDD.INVOICE_VOLUME_15) + COMPAREINT(FTH.ITEMDOCTYPE_ID, 11, '<>') * FTDD.INVOICE_VOLUME_15, FTD.DELIVERY_VOLUME, FTD.END_GROSS_STICK, FTD.END_WATER_STICK, FTD.END_VOLUME, FTD.END_TANK_TEMPERATURE, FTD.END_TANK_DENSITY, FTD.END_TANK_DENSITY_15, FTD.END_TANK_VOLUME_15, FTH.SHIFT_CONTROL_ID, FS.SHIFT_START SHIFT_START , FTD.ENV_TEMPERATURE FROM FUEL_SHIFT_CONTROL FS, DAYBATCH DB, FUEL_TANK_DELIVERY FTD, FUEL_TANK_DELIVERY_HEADER FTH, FUEL_TANK_DELIVERY_DOCUMENT FTDD WHERE FTD.SHIFT_CONTROL_ID = FS.SHIFT_CONTROL_ID AND FS.DAY_BATCH_ID = DB.DAY_BATCH_ID AND FTH.SERNUM = FTD.HEADERID AND FTH.SERNUM = FTDD.HEADERID AND FTDD.TANK_ID = FTD.TANK_ID AND FTDD.TRUCK_TANK_ID = FTD.TRUCK_TANK_ID AND FTH.STATUS in( 'A','C') AND DB.DAY_BATCH_DATE BETWEEN '{start_time}' AND '{end_time}' AND FTH.ITEMDOCTYPE_ID IN (1, 7, 8, 10, 11) UNION /* 1 - readings on start delivery */ SELECT FTD.TANK_ID, FTD.DELIVERY_DATE, 1, FTH.EXTREF, CAST(NULL AS DOUBLE PRECISION), CAST(NULL AS DOUBLE PRECISION), FTD.START_GROSS_STICK, FTD.WATER_STICK, FTD.START_VOLUME, FTD.TANK_TEMPERATURE, FTD.START_TANK_DENSITY, FTD.START_TANK_DENSITY_15, FTD.START_VOLUME_15, FTH.SHIFT_CONTROL_ID, FS.SHIFT_START SHIFT_START , CAST(20 AS DOUBLE PRECISION) FROM FUEL_SHIFT_CONTROL FS, DAYBATCH DB, FUEL_TANK_DELIVERY FTD, FUEL_TANK_DELIVERY_HEADER FTH WHERE FTD.SHIFT_CONTROL_ID = FS.SHIFT_CONTROL_ID AND FS.DAY_BATCH_ID = DB.DAY_BATCH_ID AND FTH.SERNUM = FTD.HEADERID AND FTH.STATUS in( 'A','C') AND DB.DAY_BATCH_DATE BETWEEN '{start_time}' AND '{end_time}' AND FTH.ITEMDOCTYPE_ID IN (1, 7, 8, 10, 11)'''.format(start_time=st, end_time=et) ib_session = init_interbase_connect(site.fuel_server) ib_session.execute(sql) records = ib_session.fetchall() record_obj_list = [] tank = get_tank_by_tank_id(1, site.id) previous_record = get_latest_settlement_record(tank.id) if previous_record: volum_sum = previous_record.fuel_standard_volum else: volum_sum = 0 for record in records: tank_id, original_create_time, record_type, extref, send_amount, receive_amount, altitude, water_altitude, fuel_volum, fuel_temperature, experiment_density, standard_density, fuel_standard_volum, shift_control_id, start_time, experiment_temperature = record unique_str = generate_hash(unicode(tank_id), unicode(record_type), unicode(fuel_volum), unicode(fuel_temperature), unicode(experiment_density), unicode(standard_density), unicode(fuel_standard_volum), unicode(shift_control_id)) exist = get_record_by_hash(unique_str) if exist: if exist.record_type == 3: previous_record = exist volum_sum = previous_record.fuel_standard_volum elif exist.record_type == 2: volum_sum = volum_sum + exist.receive_amount continue if tank.tank_id != tank_id: tank = get_tank_by_tank_id(tank_id, site.id) previous_record = get_latest_settlement_record(tank.id) if previous_record: volum_sum = previous_record.fuel_standard_volum else: volum_sum = 0 obj = create_record(tank_id=tank.id, original_create_time=original_create_time, record_type=record_type, send_amount=send_amount, receive_amount=receive_amount, fuel_volum=fuel_volum, fuel_temperature=fuel_temperature, experiment_density=experiment_density, standard_density=standard_density, fuel_standard_volum=fuel_standard_volum, shift_control_id=shift_control_id, hash=unique_str, belong_id=site.id, experiment_temperature=experiment_temperature, altitude=altitude, water_altitude=water_altitude, fuel_name=tank.name) if obj.record_type == 3: obj.tank_out_amount = volum_sum - fuel_standard_volum volum_sum = obj.fuel_standard_volum elif obj.record_type == 2: volum_sum = volum_sum + receive_amount obj_dict = {'record': obj, 'tank_id': tank_id} record_obj_list.append(copy.copy(obj_dict)) try: session.commit() except Exception as e: logging.exception('ERROR in commit session site {0} reason {1}'.format( site.name, e)) session.rollback() for data in record_obj_list: record = data.get('record') tank_id = data.get('tank_id') if not (record or tank_id): continue if record.record_type == 1: continue if record.record_type == 2: record.loss_amount = record.receive_amount - record.send_amount continue sql = '''SELECT SUM((isinlist(FPR.authorization_type, 'E') * FPR.CLOSE_METER + isinlist(FPR.authorization_type, 'M') * FPR.MANUAL_CLOSE_VOLUME) - (isinlist(FPH.authorization_type, 'E') * FPR.OPEN_METER + isinlist(FPH.authorization_type, 'M') * FPR.MANUAL_OPEN_VOLUME)) TOTAL FROM FUEL_PUMP_READING FPR, FUEL_PUMPS_HOSE FPH WHERE FPH.PUMP_ID = FPR.PUMP_ID AND FPH.HOSE_ID = FPR.HOSE_ID AND FPR.SHIFT_CONTROL_ID = {0} AND FPH.TANK1_ID = {1}'''.format(record.shift_control_id, tank_id) ib_session.execute(sql) totals = ib_session.fetchall() def total_add(x, y): if y[0]: return x + y[0] return x if totals: total = reduce(total_add, totals, 0) record.tanker_act_out_amount = total if record.record_type == 3: record.loss_amount = record.tanker_act_out_amount - record.tank_out_amount logging.info( 'INFO update record {0: %Y-%m-%d %H:%M:%S} success!'.format( record.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() update_site_status(site, '班结记录更新成功')
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, '商品订单更新')