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)
beijing_cut = warehouse_cut + datetime.timedelta(hours=time_dif)
# 北京统计日期前一天截单时间
beijing_yesterday_cut = beijing_cut + datetime.timedelta(days=-1)
dtdate = warehouse_date # 统计日期
dttoday = str(end_date) # 统计日期后一天
dtdate_cut = str(beijing_cut) # 统计当天北京截单时间
dtyesterday_cut = str(beijing_yesterday_cut) # 统计前一天北京截单时间
# 2、删除目标表中当前仓库当天的数据
delete_sql = "delete from %s.%s where warehouse_id = %s and dtdate = '%s'" % (
dbdest, dstable, warehouseid, dtdate)
# 调用函数来操作数据库，删除数据
cf.sql_caozuo(delete_sql, conn_dest)
# 3、查询源数据库数据
query_sql = ("""select date_sub(now(),interval %s hour) as create_time,
warehouse_id ,
wp_code,
'%s' as dtdate,
is_fba,
sm_code,
sc_code,
sum(sync_cnt) as sync_cnt,
sum(finish_ontime_cnt) as finish_ontime_cnt,
sum(finish_delay_cnt) as finish_delay_cnt,
sum(delete_cnt) as delete_cnt,
sum(submit_cnt) as submit_cnt,
sum(print_cnt) as print_cnt,
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"] = dbdest
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, dbdest))
# 表一：同步wms签出订单
dstWmsSign = 'temp_wms_sign_orders_2'
# 1、删除数据
delete_sql = ("delete from %s.%s " % (dbdest, dstWmsSign))
cf.sql_caozuo(delete_sql, conn_dest)
# 2、查询wms源数据库数据
wms_sql = (""" select warehouse_id,o.customer_code,'订单' as ds_code,
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))
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)
on o.order_code = bba.bb_refer_code
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 '已下架'
sum(case currencycode when 'RMB' then balance else 0 end) as RMB,
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)
p.ProductDeclaredName,
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,