# -*- coding: utf-8 -*-
'''
内容订单数据(全量同步，订单量多了，再用增量),定时任务，每10分钟运行一次
'''
import logging
import os
import sys
from common.mysql_uitl import fetch_all, save_result, save_etl_log
from common.time_util import now_str, YMDHMS_FORMAT
logging.basicConfig(format="%(asctime)s %(name)s:%(levelname)s:%(message)s", datefmt="%Y-%m-%d %H:%M:%S", level=logging.INFO)
file_name = sys.argv[0]
task_file = os.path.split(__file__)[-1].split(".")[0]
def dws_user_content_order(data_dt):
# 新课程订单
course_order_dict = query_dws_course_order()
row1 = save_result('tamp_data_dws', 'dws_user_content_order', course_order_dict, file_name)
# 老课程订单
old_course_order_dict = query_dws_old_course_order()
row2 = save_result('tamp_data_dws', 'dws_user_content_order', old_course_order_dict, file_name)
# 购买探普贝
recharge_order_dict = query_dws_recharge_order()
row3 = save_result('tamp_data_dws', 'dws_user_content_order', recharge_order_dict, file_name)
# 直播订单
live_order_dict = query_dws_live_order()
row4 = save_result('tamp_data_dws', 'dws_user_content_order', live_order_dict, file_name)
# 购买栏目订单
column_order_dict = query_dws_column_order()
row5 = save_result('tamp_data_dws', 'dws_user_content_order', column_order_dict, file_name)
# 购买新课程课件
course_ware_order_dict = query_dws_course_ware_order()
row6 = save_result('tamp_data_dws', 'dws_user_content_order', course_ware_order_dict, file_name)
# 购买附件
file_order_dict = query_dws_file_order()
row7 = save_result('tamp_data_dws', 'dws_user_content_order', file_order_dict, file_name)
# 线下活动订单
offline_activity_order_dict = query_dws_offline_activity_order()
row8 = save_result('tamp_data_dws', 'dws_user_content_order', offline_activity_order_dict, file_name)
row = row1 + row2 + row3 + row4 + row5 + row6 + row7 + row8
now_time = now_str(YMDHMS_FORMAT)
save_etl_log('tamp_data_dws', 'dws_user_content_order', data_dt, row, 'done', task_file, now_time)
def query_dws_course_order():
function_name = sys._getframe().f_code.co_name
logging.info(f'{function_name} start')
sql = '''
select o.id
,o.order_id
,o.data_dt
,o.user_id
,o.real_name
,o.user_name
,o.nickname
,o.team_id
,o.telephone
,o.res_id
,c.main_title as res_name
,o.payment_amount
,o.ab_pay_mode
,o.pay_mode
,o.res_type
,o.res_type_name
,o.order_type
,o.order_status
,o.transaction_no
,o.present_status
,o.group_buy_status
,o.order_number
,o.create_time
,o.pay_time
,o.complete_time
from tamp_data_dwd.dwd_user_content_order o
inner join tamp_course.course_package c
on o.res_id = c.id
where o.res_type = '300'
order by o.data_dt desc
'''
course_order_dict = fetch_all(sql, None)
logging.info(f'{function_name} success')
return course_order_dict
# 购买老课程订单
def query_dws_old_course_order():
function_name = sys._getframe().f_code.co_name
logging.info(f'{function_name} start')
sql = '''
select o.id
,o.order_id
,o.data_dt
,o.user_id
,o.real_name
,o.user_name
,o.nickname
,o.team_id
,o.telephone
,o.res_id
,c.title as res_name
,o.payment_amount
,o.ab_pay_mode
,o.pay_mode
,o.res_type
,o.res_type_name
,o.order_type
,o.order_status
,o.transaction_no
,o.present_status
,o.group_buy_status
,o.order_number
,o.create_time
,o.pay_time
,o.complete_time
from tamp_data_dwd.dwd_user_content_order o
inner join tamp_user.curriculum_res c
on o.res_id = c.id
where o.res_type in ('4', '5')
order by o.data_dt desc
'''
old_course_order_dict = fetch_all(sql, None)
logging.info(f'{function_name} success')
return old_course_order_dict
# 购买探普贝，充值订单
def query_dws_recharge_order():
function_name = sys._getframe().f_code.co_name
logging.info(f'{function_name} start')
sql = '''
select o.id
,o.order_id
,o.data_dt
,o.user_id
,o.real_name
,o.user_name
,o.nickname
,o.team_id
,o.telephone
,o.res_id
,'' as res_name
,o.payment_amount
,o.ab_pay_mode
,o.pay_mode
,o.res_type
,o.res_type_name
,o.order_type
,o.order_status
,o.transaction_no
,o.present_status
,o.group_buy_status
,o.order_number
,o.create_time
,o.pay_time
,o.complete_time
from tamp_data_dwd.dwd_user_content_order o
where o.res_type = '6'
order by o.data_dt desc
'''
course_order_dict = fetch_all(sql, None)
logging.info(f'{function_name} success')
return course_order_dict
# 直播订单
def query_dws_live_order():
function_name = sys._getframe().f_code.co_name
logging.info(f'{function_name} start')
sql = '''
select o.id
,o.order_id
,o.data_dt
,o.user_id
,o.real_name
,o.user_name
,o.nickname
,o.team_id
,o.telephone
,o.res_id
,c.zt_name as res_name
,o.payment_amount
,o.ab_pay_mode
,o.pay_mode
,o.res_type
,o.res_type_name
,o.order_type
,o.order_status
,o.transaction_no
,o.present_status
,o.group_buy_status
,o.order_number
,o.create_time
,o.pay_time
,o.complete_time
from tamp_data_dwd.dwd_user_content_order o
inner join tamp_zhibo.zhibo_theme c
on o.res_id = c.id
where o.res_type = '3'
order by o.data_dt desc
'''
course_order_dict = fetch_all(sql, None)
logging.info(f'{function_name} success')
return course_order_dict
# 栏目订单
def query_dws_column_order():
function_name = sys._getframe().f_code.co_name
logging.info(f'{function_name} start')
sql = '''
select o.id
,o.order_id
,o.data_dt
,o.user_id
,o.real_name
,o.user_name
,o.nickname
,o.team_id
,o.telephone
,o.res_id
,c.title as res_name
,o.payment_amount
,o.ab_pay_mode
,o.pay_mode
,o.res_type
,o.res_type_name
,o.order_type
,o.order_status
,o.transaction_no
,o.present_status
,o.group_buy_status
,o.order_number
,o.create_time
,o.pay_time
,o.complete_time
from tamp_data_dwd.dwd_user_content_order o
inner join tamp_user.curriculum_column c
on o.res_id = c.id
where o.res_type = '1'
order by o.data_dt desc
'''
course_order_dict = fetch_all(sql, None)
logging.info(f'{function_name} success')
return course_order_dict
# 购买新课程课件
def query_dws_course_ware_order():
function_name = sys._getframe().f_code.co_name
logging.info(f'{function_name} start')
sql = '''
select o.id
,o.order_id
,o.data_dt
,o.user_id
,o.real_name
,o.user_name
,o.nickname
,o.team_id
,o.telephone
,o.res_id
,c.original_name as res_name
,o.payment_amount
,o.ab_pay_mode
,o.pay_mode
,o.res_type
,o.res_type_name
,o.order_type
,o.order_status
,o.transaction_no
,o.present_status
,o.group_buy_status
,o.order_number
,o.create_time
,o.pay_time
,o.complete_time
from tamp_data_dwd.dwd_user_content_order o
inner join tamp_user.cs_file_record c
on o.res_id = c.guid
where o.res_type = '305'
order by o.data_dt desc
'''
course_ware_order_dict = fetch_all(sql, None)
logging.info(f'{function_name} success')
return course_ware_order_dict
def query_dws_file_order():
function_name = sys._getframe().f_code.co_name
logging.info(f'{function_name} start')
sql = '''
select o.id
,o.order_id
,o.data_dt
,o.user_id
,o.real_name
,o.user_name
,o.nickname
,o.team_id
,o.telephone
,o.res_id
,c.remark as res_name
,o.payment_amount
,o.ab_pay_mode
,o.pay_mode
,o.res_type
,o.res_type_name
,o.order_type
,o.order_status
,o.transaction_no
,o.present_status
,o.group_buy_status
,o.order_number
,o.create_time
,o.pay_time
,o.complete_time
from tamp_data_dwd.dwd_user_content_order o
inner join tamp_zhibo.zhibo_file c
on o.res_id = c.id
where o.res_type = '7'
order by o.data_dt desc
'''
file_order_dict = fetch_all(sql, None)
logging.info(f'{function_name} success')
return file_order_dict
# 线下活动订单
def query_dws_offline_activity_order():
function_name = sys._getframe().f_code.co_name
logging.info(f'{function_name} start')
sql = '''
select o.id
,o.order_id
,o.data_dt
,o.user_id
,o.real_name
,o.user_name
,o.nickname
,o.team_id
,o.telephone
,o.res_id
,c.activity_title as res_name
,o.payment_amount
,o.ab_pay_mode
,o.pay_mode
,o.res_type
,o.res_type_name
,o.order_type
,o.order_status
,o.transaction_no
,o.present_status
,o.group_buy_status
,o.order_number
,o.create_time
,o.pay_time
,o.complete_time
from tamp_data_dwd.dwd_user_content_order o
inner join tamp_jifen.offline_activity_config c
on o.res_id = c.activity_id
where o.res_type = '323'
order by o.data_dt desc
'''
file_order_dict = fetch_all(sql, None)
logging.info(f'{function_name} success')
return file_order_dict
if __name__ == '__main__':
import datetime
begin = datetime.date(2021, 9, 15)
end = datetime.date(2021, 9, 22)
data_dt = begin
delta = datetime.timedelta(days=1)
while data_dt <= end:
print(data_dt.strftime("%Y-%m-%d"))
dws_user_content_order(data_dt)
data_dt += delta