DB_CONF_FILE = sys.argv[1]
CONF_FILE = sys.argv[2]
data = json.load(open(CONF_FILE))
BASE_APPLICATON = data['BASE_APPLICATON']
COD_COUNTRY = data['COD_COUNTRY']
COD_INTEGRATION = data['COD_INTEGRATION']
DSC_PROCESS = data['DSC_PROCESS']
COD_SOURCE_SYSTEM = data['COD_SOURCE_SYSTEM']
conn = getDatabaseConnection(DB_CONF_FILE)
cur = conn.cursor()
#Begin scai execution
scai.integrationStart(DB_CONF_FILE, COD_INTEGRATION, COD_COUNTRY) # SCAI
scai.processStart(DB_CONF_FILE, DSC_PROCESS, COD_INTEGRATION,
COD_COUNTRY) # SCAI
try:
cur.execute(
" select token "\
" from crm_integration_anlt.t_lkp_token "\
" where 1 = 1 "\
" and application = '" + BASE_APPLICATON + "' ")
except Exception as e:
scai.processEnd(DB_CONF_FILE, DSC_PROCESS, COD_INTEGRATION, COD_COUNTRY,
'', '', 3) # SCAI
scai.integrationEnd(DB_CONF_FILE, COD_INTEGRATION, COD_COUNTRY, 3) # SCAI
print(e)
def main(conf_file, dml_file, country):
print(datetime.now().time())
print('Connecting to Database...')
conn = getDatabaseConnection(conf_file)
cur = conn.cursor()
country_execution_status = scai.getCountryIntegrationStatus(conf_file, country) # SCAI
scai_last_execution_status = scai.getLastExecutionStatus(conf_file, COD_INTEGRATION, country) # SCAI
if (country_execution_status != 1 and scai_last_execution_status == 1):
print ('The integration executed successfuly on last execution. The problem is further ahead.')
sys.exit(0)
if (scai_last_execution_status == 2):
sys.exit("The integration is already running...")
#If last execution ended in error, then check in which block it ended
cur.execute("select "\
" nvl(block_nbr,1) as block_nbr "\
" from crm_integration_anlt.t_rel_scai_country_integration country_integration "\
" where "\
" country_integration.cod_integration = %(COD_INTEGRATION)d "\
" and country_integration.cod_country = %(cod_country)d "\
" and country_integration.cod_status = 3 "\
" and ind_active = 1 "\
% {
'cod_country':country ,
'COD_INTEGRATION':COD_INTEGRATION
}
)
conn.commit()
results = cur.fetchone()
#If above query does not return a value (For example on a normal execution, without previous errors)
if (not results):
block_nbr = 1
else:
block_nbr = results[0]
scai.integrationStart(conf_file, COD_INTEGRATION, country) # SCAI
dml_scripts = open(dml_file).read().split('$$$')
#print('Scripts: ' + dml_scripts)
print('Executing DML scripts...')
i = 1
for dml in dml_scripts:
if i < block_nbr: # Make this run starting from a certain block
i = i + 1
continue
print('Running block #' + str(i))
try:
cur.execute("lock crm_integration_anlt.t_rel_scai_integration_process in exclusive mode")
cur.execute(dml)
except Exception as e:
conn.rollback()
scai.integrationEnd(conf_file, COD_INTEGRATION, country, 3, i) # SCAI
print (e)
print (e.pgerror)
sys.exit("The process aborted with error.")
else:
conn.commit()
i = i + 1
print('Closing Database connection...')
cur.close()
conn.close()
scai.integrationEnd(conf_file, COD_INTEGRATION, country, 1) # SCAI
print(datetime.now().time())
print('All done!')
client = basecrm.Client(access_token=base_api_token)
conn = getDatabaseConnection(conf_file)
cur = conn.cursor()
scai_process_status = scai.processCheck(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY, 1) # SCAI
#First time ever execution
if (not scai_process_status):
scai_process_status = 1
if (scai_process_status != 1):
sys.exit("The integration is already running or there was an error with the last execution that has to be fixed manually.")
scai.integrationStart(conf_file, COD_INTEGRATION, COD_COUNTRY) # SCAI
scai.processStart(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY) # SCAI
print('Start Truncate aut_otodompl_base_to_bd_contact: ' + time.strftime("%H:%M:%S"))
cur.execute("truncate table crm_integration_anlt.aut_otodompl_base_to_bd_contact; ")
print('End Truncate aut_otodompl_base_to_bd_contact: ' + time.strftime("%H:%M:%S"))
print('Start Truncate aut_otodompl_base_to_bd_deal: ' + time.strftime("%H:%M:%S"))
cur.execute("truncate table crm_integration_anlt.aut_otodompl_base_to_bd_deal; ")
print('End Truncate aut_otodompl_base_to_bd_deal: ' + time.strftime("%H:%M:%S"))
aut_otodompl_base_to_bd_contacts.main(conf_file)
aut_otodompl_base_to_bd_deals.main(conf_file)
print('Starting Query: ' + time.strftime("%H:%M:%S"))
def main(conf_file):
print('Starting Process... ' + time.strftime("%H:%M:%S"))
global BASE_API_TOKEN
BASE_API_TOKEN = json.load(open(conf_file))['base_api_token_olxpt']
client = getBaseConnection()
# Create Redshift Connection
conn = getDatabaseConnection(conf_file)
cur = conn.cursor()
scai_process_status = scai.processCheck(conf_file, scai_process_name,
COD_INTEGRATION, COD_COUNTRY,
1) # SCAI
#First time ever execution
if (not scai_process_status):
scai_process_status = 1
#if (scai_process_status != 1):
# sys.exit("The integration is already running or there was an error with the last execution that has to be fixed manually.")
scai.integrationStart(conf_file, COD_INTEGRATION, COD_COUNTRY) # SCAI
scai.processStart(conf_file, scai_process_name, COD_INTEGRATION,
COD_COUNTRY) # SCAI
print('Starting Data Query... ' + time.strftime("%H:%M:%S"))
cur.execute(
"select "\
"fac.name, "\
"cast(lkp_contact.opr_contact as integer), "\
"lkp_base_user.opr_base_user, "\
"4616871 as opr_base_source, "\
"case "\
"when "\
"v_lkp_pi.cod_index_type = 1 /* VAS */ "\
"and to_date(fac.paidads_valid_to,'yyyy-mm-dd') /* active package */ < to_date(sysdate,'yyyy-mm-dd') "\
"and last_call < to_date(sysdate,'yyyy-mm-dd') -15 "\
"then 7344246 /* organico */ "\
"else 2950782 /* sales pipeline */ "\
"end as opr_stage, "\
"(fac.price * (-1))/1.23 as val_price, "\
"lkp_category.name_pt sub_category, "\
"lkp_category_parent.name_pt main_category, "\
"to_char(to_date(fac.date,'yyyy/mm/dd'),'dd/mm/yyyy') as dat_payment, "\
"fac.id_transaction, "\
"'Automation' as deal_created_by "\
"from "\
"db_atlas.olxpt_paidads_user_payments fac, "\
"db_atlas.olxpt_users lkp_atlas_user, "\
"db_atlas.olxpt_ads lkp_ad, "\
"db_atlas.olxpt_categories lkp_category, "\
"( "\
"select id opr_category, name_pt, parent_level1, parent_level2, parent_id from db_atlas.olxpt_categories lkp_category "\
"where "\
"parent_level2 is null "\
"and parent_level1 is null "\
") lkp_category_parent, "\
"crm_integration_anlt.t_lkp_contact lkp_contact, "\
"crm_integration_anlt.t_lkp_base_user lkp_base_user, "\
"crm_integration_anlt.t_lkp_paidad_index lkp_pi, "\
"crm_integration_anlt.t_lkp_paidad_index_type lkp_pit, "\
"crm_integration_anlt.v_lkp_paidad_index v_lkp_pi, "\
"(select cod_contact, to_date(max(updated_at),'yyyy-mm-dd') last_call from crm_integration_anlt.t_fac_call where cod_source_system = 16 group by cod_contact) fac_call "\
"where "\
"fac.id_user = lkp_atlas_user.id "\
"and lkp_contact.cod_source_system = 16 "\
"and lower(lkp_atlas_user.email) = lower(lkp_contact.email) "\
"and fac.id_index = lkp_pi.opr_paidad_index "\
"and lkp_pi.cod_paidad_index_type = lkp_pit.cod_paidad_index_type "\
"and lkp_pit.valid_to = 20991231 "\
"and lkp_pit.cod_source_system = 8 "\
"and lkp_pit.opr_paidad_index_type in ('ad_homepage','highlight','bundle','nnl','pushup','logo','topads','topupaccount','paid_subscription','paid_limits_single','paid_for_post') "\
"and lkp_pi.cod_source_system = 8 "\
"and fac.id_ad = lkp_ad.id "\
"and lkp_ad.category_id = lkp_category.id "\
"and lkp_contact.cod_base_user_owner = lkp_base_user.cod_base_user "\
"and lkp_base_user.cod_source_system = 16 "\
"and lkp_base_user.valid_to = 20991231 "\
"and v_lkp_pi.cod_paidad_index = lkp_pi.cod_paidad_index "\
"and isnull(lkp_category.parent_level1,-2) = lkp_category_parent.opr_category "\
"and fac.price < 0 "\
"and trunc(fac.date) > (select last_processing_datetime from crm_integration_anlt.aut_deals_insert_to_base_date where source_system = 'pthorizontal') "\
"and lkp_contact.valid_to = 20991231 "\
"and lkp_pi.valid_to = 20991231 "\
"and lkp_contact.cod_contact = fac_call.cod_contact (+); ")
result_list = cur.fetchall()
print('Ending Data Query... ' + time.strftime("%H:%M:%S"))
print('Starting Delete Dates Query... ' + time.strftime("%H:%M:%S"))
cur.execute(
"delete from crm_integration_anlt.aut_deals_insert_to_base_date where source_system = 'pthorizontal'; "
)
print('Ending Delete Dates Query... ' + time.strftime("%H:%M:%S"))
print('Starting Dates Query... ' + time.strftime("%H:%M:%S"))
cur.execute(
"insert into crm_integration_anlt.aut_deals_insert_to_base_date "\
"select "\
"'pthorizontal' as source_system, "\
"max(fac.date) "\
"from "\
"db_atlas.olxpt_paidads_user_payments fac, "\
"db_atlas.olxpt_users lkp_atlas_user, "\
"db_atlas.olxpt_ads lkp_ad, "\
"db_atlas.olxpt_categories lkp_category, "\
"( "\
"select id opr_category, name_pt, parent_level1, parent_level2, parent_id from db_atlas.olxpt_categories lkp_category "\
"where "\
"parent_level2 is null "\
"and parent_level1 is null "\
") lkp_category_parent, "\
"crm_integration_anlt.t_lkp_contact lkp_contact, "\
"crm_integration_anlt.t_lkp_base_user lkp_base_user, "\
"crm_integration_anlt.t_lkp_paidad_index lkp_pi, "\
"crm_integration_anlt.t_lkp_paidad_index_type lkp_pit, "\
"crm_integration_anlt.v_lkp_paidad_index v_lkp_pi "\
"where "\
"fac.id_user = lkp_atlas_user.id "\
"and lkp_contact.cod_source_system = 16 "\
"and lower(lkp_atlas_user.email) = lower(lkp_contact.email) "\
"and fac.id_index = lkp_pi.opr_paidad_index "\
"and lkp_pi.cod_paidad_index_type = lkp_pit.cod_paidad_index_type "\
"and lkp_pit.valid_to = 20991231 "\
"and lkp_pit.cod_source_system = 8 "\
"and lkp_pit.opr_paidad_index_type in ('ad_homepage','highlight','bundle','nnl','pushup','logo','topads','topupaccount','paid_subscription','paid_limits_single','paid_for_post') "\
"and lkp_pi.cod_source_system = 8 "\
"and fac.id_ad = lkp_ad.id "\
"and lkp_ad.category_id = lkp_category.id "\
"and lkp_contact.cod_base_user_owner = lkp_base_user.cod_base_user "\
"and lkp_base_user.cod_source_system = 16 "\
"and lkp_base_user.valid_to = 20991231 "\
"and v_lkp_pi.cod_paidad_index = lkp_pi.cod_paidad_index "\
"and isnull(lkp_category.parent_level1,-2) = lkp_category_parent.opr_category "\
"and fac.price < 0 "\
"and lkp_contact.valid_to = 20991231 "\
"and lkp_pi.valid_to = 20991231; ")
conn.commit()
print('Ending Dates Query... ' + time.strftime("%H:%M:%S"))
print('Starting Deals Creations in Base... ' + time.strftime("%H:%M:%S"))
# Threading implementation
number_active_threads = 0
number_deals = len(result_list)
deals_per_thread = -(-number_deals // MAX_ACTIVE_THREADS
) # Ceiling of integer division
thread_list = []
i = 0
j = deals_per_thread
for n in range(0, MAX_ACTIVE_THREADS):
t = threading.Thread(target=createDealsInBase,
args=(client, result_list[i:j], conf_file))
thread_list.append(t)
t.start()
print('Spawned thread #' + str(n + 1))
i = i + deals_per_thread
j = j + deals_per_thread
if j > number_deals:
j = number_deals
for t in thread_list:
t.join()
print('Ending Deals Creations in Base... ' + time.strftime("%H:%M:%S"))
scai.processEnd(conf_file, scai_process_name, COD_INTEGRATION, COD_COUNTRY,
'', '', 1) # SCAI
scai.integrationEnd(conf_file, COD_INTEGRATION, COD_COUNTRY, 1) # SCAI
cur.close()
conn.close()
print('Ending Process... ' + time.strftime("%H:%M:%S"))