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,
