def get_tank_temperature(site): site = get_site_by_slug(site) ib_session = init_interbase_connect(site.fuel_server) for tank_id in range(1, 10): # sql = 'SELECT FDT.TEMPERATURE,FDT.READ_TIME, FT.TANK_ID, FT.TANK_NAME FROM FUEL_DAY_TEMPERATURE FDT, FUEL_TANKS FT WHERE FT.TANK_ID = {0} ORDER BY FDT.READ_TIME DESC'.format( sql = 'SELECT TEMPERATURE, READ_TIME FROM FUEL_DAY_TEMPERATURE where TANK_ID = {0} order by READ_TIME DESC'.format( tank_id) ib_session.execute(sql) res = ib_session.fetchone() if not res: logging.info( '{0}:no temperature result for site {1} tank {2}'.format( 'get_tank_temperature', site.name, tank_id)) break tank = get_tank_by_tank_id(tank_id, site.id) tank.temperature = res[0] tank.original_create_time = add_timezone_to_naive_time(res[1]) logging.info( 'INFO read temperature for site {0} tank {1} success'.format( site.name, tank_id)) 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_tank_info(site): site = get_site_by_slug(site) ib_session = init_interbase_connect(site.fuel_server) sql = 'SELECT TANK_ID ,TANK_NAME ,VOLUME_QTY ,ALARM_QTY, GRADE_PLU FROM FUEL_TANKS' ib_session.execute(sql) res = ib_session.fetchall() for itm in res: tank_id, tank_name, max_value, min_value, grade_id = itm tank_name = tank_name.strip().decode('gbk') obj = get_tank_by_tank_id(itm[0], site.id, tank_id=tank_id, name=tank_name, max_value=max_value, min_value=min_value, grade_id=grade_id) obj.max_value = max_value obj.min_value = min_value obj.grade_id = grade_id 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_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_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_pinyin(): p = Pinyin() for results in query_by_pagination(session, GoodsInventory): for itm in results: itm.py = p.get_initials(itm.name, '').replace(' ', '') try: session.commit() except Exception as e: logging.exception( 'ERROR in commit session site {0} reason {1}'.format( 'null', e)) session.rollback()
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 create_object(obj_class, **kwargs): obj = obj_class() obj.create_time = get_now_time_with_timezone() for k, v in kwargs.items(): if isinstance(v, datetime.datetime): v = add_timezone_to_naive_time(v) setattr(obj, k, v) session.add(obj) 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() logging.info('INFO create obj {0: %Y-%m-%d %H:%M:%S} success'.format( obj.original_create_time))
def create_tank(tid, site_id, *args, **kwargs): obj = FuelTank() obj.tank_id = tid, obj.name = '' obj.belong_id = site_id obj.create_time = get_now_time_with_timezone() for k, v in kwargs.items(): setattr(obj, k, v) session.add(obj) try: session.commit() except Exception as e: logging.exception('ERROR in commit session site {0} reason {1}'.format( site_id, e)) session.rollback() return obj
def refresh_goods_gpm(): for orders in query_by_pagination(session, GoodsOrder, limit=200): for order in orders: site = Site() site.id = order.belong_id inventory = get_goods_inventory_by_barcode(order.barcode, site) if not inventory.cost: continue profit = order.price - inventory.cost order.gpm = profit / order.price order.cost = inventory.cost try: session.commit() except Exception as e: logging.exception('ERROR in commit session site {0} reason {1}'.format('null', e)) session.rollback() logging.info('INFO finish refresh goods gpm')
def create_card_record(**kwargs): obj = CardRecord() obj.create_time = get_now_time_with_timezone() obj.modify_time = get_now_time_with_timezone() for k, v in kwargs.items(): if isinstance(v, datetime.datetime): v = add_timezone_to_naive_time(v) setattr(obj, k, v) session.add(obj) 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() logging.info('INFO create card record {0} success'.format( obj.original_create_time))
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 update_goods_inventory(hash_str, **kwargs): gi = get_obj_by_hash(hash_str, GoodsInventory) if not gi: gi = GoodsInventory() gi.create_time = get_now_time_with_timezone() gi.original_create_time = get_now_time_with_timezone() gi.last_sell_time = add_timezone_to_naive_time( datetime.datetime(2017, 1, 1)) for k, v in kwargs.items(): setattr(gi, k, v) gi.modify_time = get_now_time_with_timezone() session.add(gi) 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 gi
def on_success(self, retval, task_id, args, kwargs): now = get_now_time_with_timezone() site = get_site_by_slug(args[0]) logging.info('task done: {0}'.format(retval)) log = CeleryLog() log.task_id = task_id log.task_type = 1 log.status = 1 log.err_info = '' log.create_time = now log.modify_time = now log.belong_id = site.id log.original_create_time = now session.add(log) try: session.commit() except Exception as e: logging.exception( 'ERROR in commit session task {0} reason {1}'.format( task_id, e)) session.rollback() return super(OilTask, self).on_success(retval, task_id, args, kwargs)
def create_abnormal_record(abnormal_type, **kwargs): if abnormal_type == 1: st, et = get_today_st_et() else: st, et = get_week_st_et() obj = AbnormalRecord() obj.create_time = get_now_time_with_timezone() obj.start_time = st obj.end_time = et obj.abnormal_type = abnormal_type for k, v in kwargs.items(): setattr(obj, k, v) session.add(obj) 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() logging.info( 'INFO create abnormal record {0: %Y-%m-%d %H:%M:%S} success'.format( obj.create_time)) return obj
def on_failure(self, exc, task_id, args, kwargs, einfo): now = get_now_time_with_timezone() site = get_site_by_slug(args[0]) logging.exception('task fail, reason: {0}'.format(exc)) log = CeleryLog() log.task_id = task_id log.task_type = 1 log.status = 0 log.create_time = now log.modify_time = now log.original_create_time = now log.err_info = einfo log.belong_id = site.id session.add(log) try: session.commit() except Exception as e: logging.exception( 'ERROR in commit session task {0} reason {1}'.format( task_id, e)) session.rollback() return super(OilTask, self).on_failure(exc, task_id, args, kwargs, einfo)
def get_tank_value(site): site = get_site_by_slug(site) ib_session = init_interbase_connect(site.fuel_server) for tank_id in range(1, 10): sql = 'SELECT SHIFT_CONTROL_ID ,TANK_ID ,WATER_STICK ,CLOSE_QTY ,WATER_VOLUME ,TRANS_DATE FROM FUEL_TANK_READING WHERE TANK_ID = {0} ORDER BY TRANS_DATE DESC'.format( tank_id) ib_session.execute(sql) res = ib_session.fetchone() if not res: logging.info('{0}:no value result for site {1} tank {2}'.format( 'get_tank_value', site.name, tank_id)) break tank = get_tank_by_tank_id(tank_id, site.id) tank.current = res[3] if res[3] else 0 tank.original_create_time = add_timezone_to_naive_time(res[-1]) logging.info('INFO read value for site {0} tank {1} success'.format( site.name, tank_id)) 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, '商品订单更新')
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, '班结记录更新成功')