def del_finance_company_code(ids):
try:
sql = 'DELETE FROM 01_datamart_layer_007_h_cw_df.finance_company_code WHERE '
condition_sql = ''
in_codition = 'id IN {temp}'
if ids and len(ids) > 0:
group_ls = list_of_groups(ids, 1000)
for idx, group in enumerate(group_ls):
if len(group) == 1:
temp = in_codition.format(temp=str('("' + group[0] + '")'))
else:
temp = in_codition.format(temp=str(tuple(group)))
if idx == 0:
condition_sql = temp
else:
condition_sql = condition_sql + ' OR ' + temp
sql = sql + condition_sql
# log.info(sql)
prod_execute_sql(conn_type=CONN_TYPE, sqltype='insert', sql=sql)
except Exception as e:
print(e)
raise RuntimeError(e)
def query_temp_performance_bill(performance_ids):
try:
sql = 'SELECT performance_sql,order_number FROM 01_datamart_layer_007_h_cw_df.temp_performance_bill WHERE '
condition_sql = ''
in_codition = 'performance_id IN {temp}'
if performance_ids and len(performance_ids) > 0:
group_ls = list_of_groups(performance_ids, 1000)
for idx, group in enumerate(group_ls):
if len(group) == 1:
temp = in_codition.format(temp=str('("' + group[0] + '")'))
else:
temp = in_codition.format(temp=str(tuple(group)))
if idx == 0:
condition_sql = temp
else:
condition_sql = condition_sql + ' OR ' + temp
sql = sql + condition_sql
# log.info(sql)
records = prod_execute_sql(conn_type=CONN_TYPE,
sqltype='select',
sql=sql)
return records
else:
sql = sql + ' 1=1 order by order_number asc'
records = prod_execute_sql(conn_type=CONN_TYPE,
sqltype='select',
sql=sql)
return records
except Exception as e:
print(e)
raise RuntimeError(e)
def exec_sql(finance_travel_id_ls):
print('exec_sql ==> ', len(finance_travel_id_ls))
if finance_travel_id_ls and len(finance_travel_id_ls) > 0:
group_ls = list_of_groups(finance_travel_id_ls, 1000)
# print(len(group_ls), group_ls)
condition_sql = ''
in_codition = 'finance_travel_id IN {temp}'
for idx, group in enumerate(group_ls):
temp = in_codition.format(temp=str(tuple(group)))
if idx == 0:
condition_sql = temp
else:
condition_sql = condition_sql + ' OR ' + temp
# print(condition_sql)
sql = """
UPSERT into analytic_layer_zbyy_sjbyy_003_cwzbbg.finance_all_targets
select
bill_id,
'42' as unusual_id,
company_code,
account_period,
account_item,
finance_number,
cost_center,
profit_center,
'' as cart_head,
bill_code,
bill_beg_date,
bill_end_date,
'' as origin_city,
'' as destin_city,
base_beg_date as beg_date,
base_end_date as end_date,
apply_emp_name,
'' as emp_name,
'' as emp_code,
'' as company_name,
0 as jour_amount,
0 as accomm_amount,
0 as subsidy_amount,
0 as other_amount,
check_amount,
jzpz,
'办公费',
0 as meeting_amount from 01_datamart_layer_007_h_cw_df.finance_official_bill
WHERE {condition_sql}
""".format(condition_sql=condition_sql).replace('\n',
'').replace('\r',
'').strip()
# print(sql)
start_time = time.perf_counter()
prod_execute_sql(conn_type='test', sqltype='insert', sql=sql)
consumed_time = round(time.perf_counter() - start_time)
print(f'*** 执行SQL耗时 {consumed_time} sec')
def exec_sql(finance_id_ls, mean_val):
print('* exec_sql ==> ', len(finance_id_ls))
if finance_id_ls and len(finance_id_ls) > 0:
group_ls = list_of_groups(finance_id_ls, 1000)
# print(len(group_ls), group_ls)
condition_sql = ''
in_codition = 'finance_offical_id IN {temp}'
for idx, group in enumerate(group_ls):
if len(group) == 1:
temp = in_codition.format(temp=str('("' + group[0] + '")'))
else:
temp = in_codition.format(temp=str(tuple(group)))
if idx == 0:
condition_sql = temp
else:
condition_sql = condition_sql + ' OR ' + temp
# print(condition_sql)
sql = """
upsert into analytic_layer_zbyy_cwyy_014_cwzbbg.finance_all_targets
(finance_id,bill_id,unusual_id,company_code,account_period,finance_number,profit_center,cart_head,bill_code,bill_beg_date,bill_end_date,apply_emp_name,
company_name,check_amount,jzpz,target_classify,exp_type_name,appr_org_sfname,sales_address,jzpz_tax,billingdate,apply_id,base_apply_date,tb_times,receipt_city,
commodityname,iscompany,operation_time,doc_date,operation_emp_name,invoice_type_name,taxt_amount,original_tax_amount,js_times,invo_number,invo_code,
amounttax,taxtp_name,approve_name,mean,importdate)
select
finance_offical_id,bill_id,'49',company_code,account_period,finance_number,profit_center,cart_head,bill_code,bill_beg_date,bill_end_date,apply_emp_name,
company_name,check_amount,jzpz,'办公费',exp_type_name,appr_org_sfname,sales_address,jzpz_tax,billingdate,apply_id,base_apply_date,tb_times,receipt_city,
commodityname,isCompany,operation_time,doc_date,operation_emp_name,invoice_type_name,taxt_amount,original_tax_amount,js_times,invo_number,invo_code,
amounttax,taxtp_name,approve_name,{mean_val},importdate
from 01_datamart_layer_007_h_cw_df.finance_official_bill
WHERE {condition_sql}
""".format(condition_sql=condition_sql, mean_val=mean_val)
#print(sql)
try:
start_time = time.perf_counter()
prod_execute_sql(conn_type=CONN_TYPE, sqltype='insert', sql=sql)
consumed_time = round(time.perf_counter() - start_time)
print(f'*** 执行SQL耗时 {consumed_time} sec')
except Exception as e:
print(e)
raise RuntimeError(e)
def exec_temp_api_bill_sql_by_ids(tem_api_ids):
"""
执行临时表API表,
:param tem_api_ids : 临时表的主键ids
:return:
"""
try:
sql = "SELECT api_sql,tem_api_id FROM 01_datamart_layer_007_h_cw_df.temp_api_bill WHERE "
condition_sql = ''
in_codition = 'tem_api_id IN {temp}'
if tem_api_ids and len(tem_api_ids) > 0:
group_ls = list_of_groups(tem_api_ids, 1000)
for idx, group in enumerate(group_ls):
if len(group) == 1:
temp = in_codition.format(temp=str('("' + group[0] + '")'))
else:
temp = in_codition.format(temp=str(tuple(group)))
if idx == 0:
condition_sql = temp
else:
condition_sql = condition_sql + ' OR ' + temp
sql = sql + condition_sql
log.info(sql)
records = prod_execute_sql(conn_type=CONN_TYPE,
sqltype='select',
sql=sql)
for record in records:
api_sql = str(record[0])
tem_api_id = str(record[1])
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=api_sql)
log.info(
f'成功执行了tem_api_id为{tem_api_id}的临时表的SQL, 共有{len(records)}条SQL '
)
except Exception as e:
#print(e)
error_info = str(e)
raise RuntimeError(e)
def exec_sql(self, bill_code_ls):
print('checkpoint_12 exec_sql ==> ', len(bill_code_ls))
if bill_code_ls and len(bill_code_ls) > 0:
group_ls = list_of_groups(bill_code_ls, 1000)
# print(len(group_ls), group_ls)
condition_sql = ''
in_codition = 'bill_code IN {temp}'
for idx, group in enumerate(group_ls):
if len(group) == 1:
temp = in_codition.format(temp=str('("' + group[0] + '")'))
else:
temp = in_codition.format(temp=str(tuple(group)))
if idx == 0:
condition_sql = temp
else:
condition_sql = condition_sql + ' OR ' + temp
# print(condition_sql)
sql = """
UPSERT INTO analytic_layer_zbyy_cwyy_014_cwzbbg.finance_all_targets
SELECT
finance_travel_id as finance_id,
bill_id,
'12' as unusual_id,
company_code,
account_period,
finance_number,
profit_center,
cart_head,
bill_code,
bill_beg_date,
bill_end_date,
'' as origin_city,
'' as destin_city,
'' as beg_date,
'' as end_date,
apply_emp_name,
'' as emp_name,
'' as emp_code,
'' as company_name,
jour_amount,
accomm_amount,
subsidy_amount,
other_amount,
check_amount,
jzpz,
'差旅费' as target_classify,
0 as meeting_amount,
'' as exp_type_name,
'' as next_bill_id,
'' as last_bill_id,
appr_org_sfname,
sales_address,
'' as meet_addr,
'' as sponsor,
jzpz_tax,
billingdate,
'' as remarks,
0 as hotel_amount,
0 as total_amount,
apply_id,
base_apply_date,
'' as scenery_name_details,
'' as meet_num,
0 as diff_met_date,
0 as diff_met_date_avg,
tb_times,
receipt_city,
commodityname,
'' as category_name,
iscompany,
origin_province,
destin_province,
operation_time,
doc_date,
operation_emp_name,
invoice_type_name,
taxt_amount,
original_tax_amount,
js_times,
'' as offset_day,
'' as meet_lvl_name,
'' as meet_type_name,
0 as buget_limit,
0 as sum_person,
invo_number,
invo_code,
'' as city,
0 as amounttax,
'' as offset_ratio,
'' as amounttax_ratio,
'' as ratio,
'' as approve_name,
importdate
FROM 01_datamart_layer_007_h_cw_df.finance_travel_bill
WHERE {condition_sql}
""".format(condition_sql=condition_sql)
# print(sql)
try:
start_time = time.perf_counter()
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=sql)
consumed_time = round(time.perf_counter() - start_time)
print(f'*** 执行SQL耗时 {consumed_time} sec')
except Exception as e:
print(e)
raise RuntimeError(e)
def exec_sql(bill_id_ls):
print('exec_sql ==> ', len(bill_id_ls))
if bill_id_ls and len(bill_id_ls) > 0:
group_ls = list_of_groups(bill_id_ls, 1000)
# print(len(group_ls), group_ls)
condition_sql = ''
in_codition = 'bill_id IN {temp}'
for idx, group in enumerate(group_ls):
temp = in_codition.format(temp=str(tuple(group)))
if idx == 0:
condition_sql = temp
else:
condition_sql = condition_sql + ' OR ' + temp
# print(condition_sql)
sql = """
INSERT INTO analytic_layer_zbyy_sjbyy_003_cwzbbg.finance_all_targets
SELECT uuid() AS finance_id, bill_id, '14' AS unusual_id,
company_code,
account_period,
finance_number,
cost_center,
profit_center,
cart_head,
bill_code,
bill_beg_date,
bill_end_date,
' ' AS origin_city,
' ' AS destin_city,
' ' AS beg_date,
' ' AS end_date,
apply_emp_name,
' ' AS emp_name,
' ' AS emp_code,
' ' AS company_name,
jour_amount,
accomm_amount,
subsidy_amount,
other_amount,
check_amount,
jzpz, '差旅费' AS target_classify,
0 AS meeting_amount,
' ' AS exp_type_name,
' ' AS next_bill_id,
' ' AS last_bill_id,
appr_org_sfname,
sales_address,
' ' AS meet_addr,
' ' AS sponsor,
jzpz_tax,
billingdate,
' ' AS remarks,
0 AS hotel_amount,
0 AS total_amount,
apply_id, base_apply_date,
' ' AS scenery_name_details,
' ' AS meet_num,
0 AS diff_met_date,
0 AS diff_met_date_avg,
tb_times,
receipt_city,
commodityname,
' ' AS category_name,
iscompany,
origin_province,
destin_province,
importdate FROM 01_datamart_layer_007_h_cw_df.finance_travel_bill
WHERE {condition_sql}
""".format(condition_sql=condition_sql).replace('\n', '').replace(
'\r', '').strip()
print(sql)
try:
start_time = time.perf_counter()
prod_execute_sql(conn_type='test', sqltype='insert', sql=sql)
consumed_time = round(time.perf_counter() - start_time)
print(f'*** 执行SQL耗时 {consumed_time} sec')
except Exception as e:
print(e)
raise RuntimeError(e)
def exec_sql(bill_id_ls):
log.info(f"checkpoint_13 exec_sql ==> {len(bill_id_ls)}")
if bill_id_ls and len(bill_id_ls) > 0:
group_ls = list_of_groups(bill_id_ls, 1000)
# print(len(group_ls), group_ls)
condition_sql = ""
in_codition = "bill_id IN {temp}"
for idx, group in enumerate(group_ls):
if len(group) == 1:
temp = in_codition.format(temp=str("(\"" + group[0] + "\")"))
else:
temp = in_codition.format(temp=str(tuple(group)))
if idx == 0:
condition_sql = temp
else:
condition_sql = condition_sql + " OR " + temp
# print(condition_sql)
sql = """
UPSERT INTO analytic_layer_zbyy_cwyy_014_cwzbbg.finance_all_targets
SELECT
hotel_bill_id as finance_id,
bill_id,
"13" as unusual_id,
"" as company_code,
"" as account_period,
"" as finance_number,
"" as profit_center,
"" as cart_head,
"" as bill_code,
"" as bill_beg_date,
"" as bill_end_date,
"" as origin_city,
"" as destin_city,
beg_date,
end_date,
"" as apply_emp_name,
emp_name,
emp_code,
"" as company_name,
0 as jour_amount,
0 as accomm_amount,
0 as subsidy_amount,
0 as other_amount,
0 as check_amount,
0 as jzpz,
"差旅费" as target_classify,
0 as meeting_amount,
exp_type_name,
"" as next_bill_id,
"" as last_bill_id,
"" as appr_org_sfname,
"" as sales_address,
"" as meet_addr,
"" as sponsor,
0 as jzpz_tax,
"" as billingdate,
"" as remarks,
hotel_amount,
total_amount,
"" as apply_id,
"" as base_apply_date,
"" as scenery_name_details,
"" as meet_num,
0 as diff_met_date,
0 as diff_met_date_avg,
"" as tb_times,
"" as receipt_city,
"" as commodityname,
"" as category_name,
"" as iscompany,
"" as origin_province,
"" as destin_province,
"" as operation_time,
"" as doc_date,
"" as operation_emp_name,
invoice_type_name,
0 as taxt_amount,
0 as original_tax_amount,
"" as js_times,
"" as offset_day,
"" as meet_lvl_name,
"" as meet_type_name,
0 as buget_limit,
0 as sum_person,
"" as invo_number,
"" as invo_code,
"" as city,
0 as amounttax,
"" as offset_ratio,
"" as amounttax_ratio,
"" as approve_name,
"" as ratio,
importdate
FROM 01_datamart_layer_007_h_cw_df.finance_rma_travel_accomm
WHERE {condition_sql}
""".format(condition_sql=condition_sql)
# print(sql)
try:
start_time = time.perf_counter()
prod_execute_sql(conn_type=CONN_TYPE, sqltype="insert", sql=sql)
consumed_time = round(time.perf_counter() - start_time)
print(f"*** 执行SQL耗时 {consumed_time} sec")
except Exception as e:
print(e)
raise RuntimeError(e)