and add_time <= '2020-07-10 00:00:00' and charge_type in (0,2) group by customer_code, order_number, serial_number, charge_type, flow_type, add_time """ print(query_sql) res_df = pd.read_sql(query_sql, conn) print(res_df) # 查询mysql中orders表,条件为未删除的订单 no_delete_sql = """ select order_code as ordernumber from orders where order_status != 0 group by order_code """ print(no_delete_sql) no_delete_df = pd.read_sql(no_delete_sql, engine_wms) print(no_delete_df) # 过滤未删除的订单 pd.merge(res_df, no_delete_df, how='inner', on='ordernumber') print(res_df) # 将结果数据写进mysql数据库 # 1.删除原有数据 del_sql = "delete from %s.%s" % (dstDb, dstTb) cf.sql_caozuo(del_sql, conn_dest) # 2.结果更新写入数据库 cf.df_to_sql(engine_dest, res_df, dstTb)
0 as finish_ontime_cnt, 0 as finish_delay_cnt, 0 as delete_cnt, 0 as submit_cnt, 0 as print_cnt, 0 as pack_cnt, 0 as not_off_cnt , 0 as off_error_cnt , 0 as untreated_abnormal_cnt , 0 as finish_abnormal_cnt , 0 as not_pick_cnt , 0 as not_distri_cnt , 0 as history_unfinish_cnt , count(1) as history_finish_cnt from order_operation_time ot join orders o on o.order_id =ot.order_id join order_physical_relation opr on opr.order_code=o.order_code where ship_time >=date_add('%s',interval %s hour) and call_wms_time<='%s' and order_status=8 and o.customer_code not in('000010','000016') group by o.warehouse_id,wp_code,is_fba,sm_code, sc_code )t group by warehouse_id,wp_code,is_fba,sm_code, sc_code""" % (time_dif, dtdate, dttoday, time_dif, dttoday, time_dif, dtyesterday_cut, dtyesterday_cut, dtdate_cut, dtyesterday_cut, dtdate, time_dif, dtyesterday_cut)) print(query_sql) res_df = pd.read_sql(query_sql, engine_source) # 4、将查询出来的数据存入目标表 cf.df_to_sql(engine_dest, res_df, dstable)
conn_dest["port"] = fbg_mid_port conn_dest["user"] = fbg_mid_username conn_dest["passwd"] = fbg_mid_password conn_dest["db"] = dstDb conn_dest["charset"] = "utf8" engine_dest = ("mysql+pymysql://%s:%s@%s:%d/%s?charset=utf8" % (fbg_mid_username, fbg_mid_password, fbg_mid_hostname, fbg_mid_port, dstDb)) # 获取ods订单号 query_ods = "select order_code from orders where order_type = 10" orders_df = pd.read_sql(query_ods, engine_ods) print(orders_df) orders_str = "','".join(list(orders_df.order_code)) print(orders_str) # wms库的费用详情 query_wms = """select cbl_refer_code, currency_code, case when cbl_type = 2 then cbl_value else 0 end as out_amount, case when cbl_type = 3 then cbl_value else 0 end as in_amount from customer_balance_log cbl where ft_code = 'FBAO' and cbl_refer_code in ('%s')""" % orders_str print(query_wms) bill_df = pd.read_sql(query_wms, engine_wms) print(bill_df) # 将结果写进mid库-先删除再写入 del_sql = "delete from %s.%s" % (dstDb, dstTb) cf.sql_caozuo(del_sql, conn_dest) cf.df_to_sql(engine_dest, bill_df, dstTb)
0 as finish_delay_cnt, 0 as delete_cnt, 0 as submit_cnt, 0 as print_cnt, 0 as pack_cnt, 0 as not_off_cnt , 0 as off_error_cnt , 0 as untreated_abnormal_cnt , 0 as finish_abnormal_cnt , 0 as not_pick_cnt , 0 as not_distri_cnt , 0 as history_unfinish_cnt , count(1) as history_finish_cnt from order_operation_time ot join orders o on o.order_id =ot.order_id join order_physical_relation opr on opr.order_code=o.order_code where ship_time >=date_add('%s',interval %s hour) and call_wms_time<='%s' and order_status=8 and o.customer_code not in('000010','000016') group by o.warehouse_id,wp_code,is_fba,sm_code, sc_code )t group by warehouse_id,wp_code,is_fba,sm_code, sc_code""" % (time_dif, dtdate, dttoday, time_dif, dttoday, time_dif, dtyesterday_cut, dtyesterday_cut, dtdate_cut, dtyesterday_cut, dtdate, time_dif, dtyesterday_cut)) print(query_sql) # 结果保存在字典query_df中 query_df = cf.sql_to_df(engine_source, query_sql) # 4、将查询出来的数据存入目标表 cf.df_to_sql(engine_dest, query_df, dstable)
union all select wsc_code,receiving_code from wh_storage_charges as wsc left join (SELECT receiving_code, reservation_number from reservation_order ro join reservation_order_detail rod on ro.ro_id = rod.ro_id) b on wsc.reference_code = b.reservation_number where wsc_type = 2 """) print(query_charge) charg_df = pd.read_sql(query_charge, engine_source) # 第一种情况:已计费入库费 charged_df = receive_df.merge(charg_df, how='inner', on=["receiving_code"]) print(charged_df.columns) print(charged_df) # 第二种情况:未计费入库费--取差集 no_charged_df = receive_df.append( charged_df.drop(columns='wsc_code')).drop_duplicates(keep=False) print(no_charged_df) # 先删除表中旧数据 del_chargedTb = "delete from %s.%s" % (dstDb, chargedTb) del_noChargeTb = "delete from %s.%s" % (dstDb, noChargeTb) cf.sql_caozuo(del_chargedTb, conn_dest) cf.sql_caozuo(del_noChargeTb, conn_dest) # 写入结果表 cf.df_to_sql(engine_dest, charged_df, chargedTb) cf.df_to_sql(engine_dest, no_charged_df, noChargeTb)
dstTb = 't_bsc_monitor_warehouseorder_repeat' # 构建目标数据库连接字典 conn_dest = dict() conn_dest["host"] = fbg_mid_hostname conn_dest["port"] = fbg_mid_port conn_dest["user"] = fbg_mid_username conn_dest["passwd"] = fbg_mid_password conn_dest["db"] = dstDb conn_dest["charset"] = "utf8" engine_dest = ("mysql+pymysql://%s:%s@%s:%d/%s?charset=utf8" % (fbg_mid_username, fbg_mid_password, fbg_mid_hostname, fbg_mid_port, dstDb)) bsc_sql = """ select Order_Number, COUNT(1) as repeat_cnt FROM fbg_busi.fbg_busi_bsc_warehouseorder_m where business_type = 'as' group by Order_Number having repeat_cnt > 1 """ bsc_df = pd.read_sql(bsc_sql, conn) print(bsc_df) # 删除原有数据 del_sql = "delete from %s.%s" % (dstDb, dstTb) cf.sql_caozuo(del_sql, conn_dest) # # 写入目标表 cf.df_to_sql(engine_dest, bsc_df, dstTb)
dstdb = 'fbg_mid_dw' dsttb = 't_tmp_bsc_check_tremonth_flow' # 构建目标数据库连接字典 conn_dest = dict() conn_dest["host"] = fbg_mid_hostname conn_dest["port"] = fbg_mid_port conn_dest["user"] = fbg_mid_username conn_dest["passwd"] = fbg_mid_password conn_dest["db"] = dstdb conn_dest["charset"] = "utf8" engine_dest = ("mysql+pymysql://%s:%s@%s:%d/%s?charset=utf8" % (fbg_mid_username, fbg_mid_password, fbg_mid_hostname, fbg_mid_port, dstdb)) # 查询历史数据 query_sql = ( "SELECT customerCode, orderNumber, serialNumber, ChargeType, flowtype, AddTime" " from %s.%s " "where addTime >= '2020-06-01 00:00:00' and addTime < '2020-06-17 00:00:00' " "and chargeType = 0") % (srcdb, srctb) print(query_sql) query_df = pd.read_sql(query_sql, engine_source) print(query_df) # 先删除历史数据 del_sql = "delete from %s.%s" % (dstdb, dsttb) cf.sql_caozuo(del_sql, conn_dest) # 将结果写入结果表 cf.df_to_sql(engine_dest, query_df, dsttb)
o.order_code,ship_time ,create_currency_code, sum(bi_amount) order_amount from orders o join order_operation_time ot on o.order_id =ot.order_id left join bil_business_attach bba on bba.bb_refer_code=o.order_code left join bil_income bi on bba.bb_id =bi.bb_id where ship_time>='%s' and ship_time<'%s' group by warehouse_id,o.customer_code,o.order_code,ship_time ,create_currency_code""" % (startdate, enddate)) wms_df = pd.read_sql(wms_sql, engine_wms_source) # 3、将查询出来的数据存入目标表 cf.df_to_sql(engine_dest, wms_df, dstWmsSign) # 表二:同步表bsc费用明细订单的流水金额 dstBscFlow = 'temp_bsc_warehouseorder_2' # 删除数据 delete_sql2 = ("delete from %s.%s " % (dbdest, dstBscFlow)) cf.sql_caozuo(delete_sql2, conn_dest) # 2、查询源数据库数据 df_list = [] for bsc_sourceDB in bsc_sourceDB_list: query_bsc_sql = ("""select wo.OrderNumber , sum(case when FlowType=0 then Amount else 0 end ) as in_money, sum(case when FlowType=1 then Amount else 0 end ) as out_money from %s.warehouseorder wo left join %s.warehousbillflow wb
bi_status, bi_amount, currency_code FROM bil_business bb JOIN bil_business_attach bba ON bb.bb_id=bba.bb_id JOIN bil_income bi ON bi.bb_id=bba.bb_id WHERE ds_code='as' and bb_charge_time >= '%s' and bb_charge_time <= '%s' )bb ON bb.bb_refer_code =asro.asro_code where asro_add_time >= '%s' and asro_add_time <= '%s' and asro_code in ('%s') group by asro_code;""" % (start_date, end_date, start_date, end_date, returnNum_str) print(query_wms_total) df_total = pd.read_sql(query_wms_total, engine_wms) # 删除原有数据 del_class = "delete from %s.%s" % (dstDb, dstTb_class) cf.sql_caozuo(del_class, conn_dest) del_total = "delete from %s.%s" % (dstDb, dstTb_total) cf.sql_caozuo(del_total, conn_dest) # 3.结果更新写入数据库 cf.df_to_sql(engine_dest, df_class, dstTb_class) cf.df_to_sql(engine_dest, df_total, dstTb_total)
join bil_income bi on bba.bb_id = bi.bb_id join warehouse w on o.warehouse_id = w.warehouse_id where o.add_time >= '%s' and o.add_time < '%s' group by o.order_code, w.warehouse_code, o.sm_code, o.order_status; """ % (start_time, end_time) print(wms_sql) wms_df = pd.read_sql(wms_sql, engine_wms) print(wms_df) # 删数据写数据 del_wms = "delete from %s.%s" % (dstDb, wms_tb) cf.sql_caozuo(del_wms, conn_dest) cf.df_to_sql(engine_mid, wms_df, wms_tb) # ods数据 ods_tb = "t_monitor_ods_fee_charge" ods_sql = """ SELECT o.order_code, o.warehouse_code, o.sm_code, case when o.order_status = 0 then '删除' when o.order_status = 1 then '草稿' when o.order_status = 2 then '确认' when o.order_status = 3 then '异常' when o.order_status = 4 then '已提交' when o.order_status = 5 then '已打印' when o.order_status = 6 then '已下架' when o.order_status = 7 then '已打包'
date_sub(ship_time,interval %s hour) owms_ship_time from orders o join order_operation_time ot on o.order_id =ot.order_id join order_physical_relation opr on opr.order_code=o.order_code LEFT JOIN order_channel_test oc ON o.sc_code=oc.sc_code where call_wms_time>'%s' and call_wms_time<='%s' and order_status >=4 and o.customer_code not in('000010','000016') and sm_code not in ('PICKED_UP_BARTER','PICKED_UP_DESTROY','SHANG_PIN_CHAI_FEN','SHANG_PIN_HE_BING', 'TUIJIANZIXUAN','WEIYUBAOTUIJIAN','ZITI') """ % (time_zone, time_zone, dtyesterday, dtdate)) print(query_sql) # 结果保存在字典query_df中 query_df = cf.sql_to_df(owms_sourceDB, query_sql) # 3、将查询出来的数据存入目标表 cf.df_to_sql(engine_dest, query_df, owms_dstable) # 表二:同步表hms容器订单表 hms_dstable = 'temp_hms_container_details_2' # 1、删除目标表中当前仓库的数据 delete_sql2 = ("delete from %s.%s where warehouse_id=%s" % (dbdest, hms_dstable, owms_warehouseid)) # 调用函数来操作数据库,删除数据 cf.sql_caozuo(delete_sql2, conn_dest) # 2、查询源数据库数据 query_sql2 = ( """select %s as warehouse_id,cd.order_number as order_code, cd.loader_time,cd.shipper_time,created_at from container c join container_details cd on c.container_id=cd.container_id
sum(case currencycode when 'USD' then balance else 0 end) as USD, sum(case currencycode when 'EUR' then balance else 0 end) as EUR, sum(case currencycode when 'GBP' then balance else 0 end) as GBP, sum(case currencycode when 'HKD' then balance else 0 end) as HKD, sum(case currencycode when 'AUD' then balance else 0 end) as AUD, sum(case currencycode when 'JPY' then balance else 0 end) as JPY from gc_bsc_common.signbody s left join gc_bsc_amc.SignBodyBalance sb on s.AccountCode = sb.AccountCode group by CustomerCode """ yue_df = pd.read_sql(yue_sql, engine_bsc) yue_tb = "t_tmp_yue" del_yue = "delete from %s.%s" % (dbdest, yue_tb) cf.sql_caozuo(del_yue, conn_dest) cf.df_to_sql(engine_mid, yue_df, yue_tb) print('1' * 100) # 子账号数据 account_sql = """ SELECT CustomerCode, count(distinct UserCode) as cnt from gc_bsc_usercenter.users where AccountType = 1 group by CustomerCode """ account_df = pd.read_sql(account_sql, engine_bsc) account_tb = "t_tmp_account" del_account = "delete from %s.%s" % (dbdest, account_tb) cf.sql_caozuo(del_account, conn_dest) cf.df_to_sql(engine_mid, account_df, account_tb)
p.ProductDeclaredNameCN, pdc.DeclaredNameCN, pcc.CustomsClearanceNameEn, pdc.DeclaredValue from product p left join productdestinationcountry pdc on p.ProductId = pdc.ProductId join productcustomsclearance pcc on p.ProductBarcode = pcc.ProductBarcode where LENGTH(pcc.CustomsClearanceNameEn) > 30; """ bsc_df = pd.read_sql(bsc_sql, engine_bsc) bsc_Tb = "t_tmp_bsc_declaredvalue" del_bsc = "delete from fbg_mid_dw.t_tmp_bsc_declaredvalue" cf.sql_caozuo(del_bsc, conn_dest) cf.df_to_sql(engine_mid, bsc_df, bsc_Tb) wms_sql = """ select product_barcode, product_declared_value from product; """ wms_df = pd.read_sql(wms_sql, engine_wms) wms_Tb = "t_tmp_wms_declaredvalue" del_wms = "delete from fbg_mid_dw.t_tmp_wms_declaredvalue" cf.sql_caozuo(del_wms, conn_dest) cf.df_to_sql(engine_mid, wms_df, wms_Tb) res_sql = """ select b.ProductBarcode, ProductDeclaredName,