engine = sqlalchemy.create_engine(URL(**snowflake_credentials), encoding = 'latin-1')
connection = engine.connect()
fecha_inicio
fecha_final
#PRUEBA
warehouse = pd.read_sql_query("USE WAREHOUSE PAGOS",connection)
movimientos = pd.read_sql_query(
f'''select d.storekeeper_id,
case when r.id = 154 then 'iva_automatico'
when r.id = 155 then 'isr_automatico'
when r.id = 163 then 'isr_manual_movimientos'
when r.id = 164 then 'iva_manual_movimientos'
when r.id = 178 then 'state_tax'
when r.id = 183 then 'iva_penalty'
when r.id = 184 then 'isr_penalty'
end as AJUSTE,
sum(d.total) as valor_movimientos
from mx_pglr_ms_dispersion_calculator_public.paid_lots p
join mx_pglr_ms_dispersion_calculator_public.dispersion_details d on p.id = d.paid_lot_id
left join mx_core_orders_public.orders o on d.model_id = o.id and d.flow_id in (1,2,8,10,11,19,13,16,21,25)
join mx_pglr_ms_dispersion_calculator_public.reason_types r on d.reason_type_id = r.iD
join mx_pglr_ms_dispersion_calculator_public.flows f on d.flow_id = f.id
join MX_PG_MS_STOREKEEPERS_PUBLIC.storekeepers_ofuscated sk on d.storekeeper_id = sk.id and coalesce(sk._fivetran_deleted, false) = false and sk.type = 'rappitendero'
where p.balance_request is not null
and d.created_at::Date between '{fecha_inicio}'::Date and '{fecha_final}'::date
and r.id <> 52
and r.id in (155,154,161,163,164,183,184,178)
group by 1,2
order by 1''', connection)
There has been an error in the blocks above
Run the app again or contact app’s creator
movimientos_agrupados = pd.pivot_table(movimientos, values='valor_movimientos', index=['storekeeper_id'],
columns=['ajuste'], aggfunc=np.sum, fill_value=0)
There has been an error in the blocks above
Run the app again or contact app’s creator
mes_bd
bd_liliana = pd.read_csv(f'bd_liliana_{mes_bd}.csv', sep = ';')
There has been an error in the blocks above
Run the app again or contact app’s creator
mes
fecha_inicio_ajustes
fecha_fin_ajustes
warehouse = pd.read_sql_query("USE WAREHOUSE PAGOS",connection)
ajustes_manuales = pd.read_sql_query(
f'''select to_char(cd.created_at, 'yyyy-mm') as mes,dd.storekeeper_id,
case when name ilike '%manual_adjustment_iva_debt%' then 'iva_ajustes_manual'
when name ilike '%manual_adjustment_isr_debt%' then 'isr_ajustes_manual'
end as tipo_ajuste,
comment, count(*) as cuenta, sum(cd.amount) as valor_ajustes_manuales
from MX_PGlr_MS_MANUAL_DISPERSION_PUBLIC.debts cd
join MX_PGlr_MS_MANUAL_DISPERSION_PUBLIC.debt_Reasons r on cd.reason = r.id
join mx_pglr_ms_dispersion_calculator_public.dispersion_details dd on cd.id = dd.model_id and dd.flow_id = 6 and state_id = 2
where CD.created_at::date between '{fecha_inicio_ajustes}'::Date and '{fecha_final_ajustes}'::date
and (name ilike '%manual_adjustment_iva_debt%' or name ilike '%manual_adjustment_isr_debt%')
and comment ilike '%{mes}%'
group by 1,2,3,4
order by 4 desc
''', connection)
There has been an error in the blocks above
Run the app again or contact app’s creator
ajustes_agrupados = pd.pivot_table(ajustes_manuales, values='valor_ajustes_manuales', index=['storekeeper_id'],
columns=['tipo_ajuste'], aggfunc=np.sum, fill_value=0)
There has been an error in the blocks above
Run the app again or contact app’s creator
mes_inicio_compensaciones
mes_final_compensaciones
warehouse = pd.read_sql_query("USE WAREHOUSE PAGOS",connection)
compensaciones = pd.read_sql_query(
f'''select dd.storekeeper_id,
case when comment ilike '%iva%' then 'iva_compensacion'
when comment ilike '%isr%' then 'isr_compensacion'
end as tipo_compensacion,
sum(dd.total) as valor_compensacion
from mx_PGLR_MS_MANUAL_DISPERSION_PUBLIC.COLLECT_DISPERSIONS cd
join mx_pglr_ms_dispersion_calculator_public.dispersion_details dd on cd.id = dd.model_id and reason_type_id = 38 and state_id = 2
where Dd.created_at::Date between '{mes_inicio_compensaciones}'::Date and '{mes_final_compensaciones}'::date
and (comment ilike '%iva%' or comment ilike '%isr%')
and comment ilike '%{mes}%'
group by 1,2''', connection)
There has been an error in the blocks above
Run the app again or contact app’s creator
compensaciones_agrupadas = pd.pivot_table(compensaciones, values='valor_compensacion', index=['storekeeper_id'],
columns=['tipo_compensacion'], aggfunc=np.sum, fill_value=0)
There has been an error in the blocks above
Run the app again or contact app’s creator
bdliliana_movimientos = bd_liliana.merge(movimientos_agrupados,how = 'left',left_on='STOREKEEPER_ID',right_on='storekeeper_id')
There has been an error in the blocks above
Run the app again or contact app’s creator
bdliliana_movimientos_ajustes = bdliliana_movimientos.merge(ajustes_agrupados,how='left', left_on='STOREKEEPER_ID', right_on='storekeeper_id')
There has been an error in the blocks above
Run the app again or contact app’s creator
tabla_final = bdliliana_movimientos_ajustes.merge(compensaciones_agrupadas,how='left',left_on='STOREKEEPER_ID',right_on='storekeeper_id')
There has been an error in the blocks above
Run the app again or contact app’s creator
tabla_final.fillna(0,inplace=True)
There has been an error in the blocks above
Run the app again or contact app’s creator
try:
for i in tabla_final.index:
tabla_final.loc[i,'diferencia_iva_query_ajustes'] = tabla_final.loc[i,'RET_IVA'] + tabla_final.loc[i,'iva_compensacion'] + tabla_final.loc[i,'iva_automatico'] + tabla_final.loc[i,'iva_ajustes_manual'] + tabla_final.loc[i,'iva_penalty'] + tabla_final.loc[i,'state_tax']
tabla_final.loc[i,'diferencia_isr_query_ajustes'] = tabla_final.loc[i,'RET_ISR'] + tabla_final.loc[i,'isr_compensacion'] + tabla_final.loc[i,'isr_automatico'] + tabla_final.loc[i,'isr_ajustes_manual'] + tabla_final.loc[i,'isr_penalty']
except:
for i in tabla_final.index:
tabla_final.loc[i,'diferencia_iva_query_ajustes'] = tabla_final.loc[i,'RET_IVA'] + tabla_final.loc[i,'iva_compensacion'] + tabla_final.loc[i,'iva_automatico'] + tabla_final.loc[i,'iva_ajustes_manual']
tabla_final.loc[i,'diferencia_isr_query_ajustes'] = tabla_final.loc[i,'RET_ISR'] + tabla_final.loc[i,'isr_compensacion'] + tabla_final.loc[i,'isr_automatico'] + tabla_final.loc[i,'isr_ajustes_manual']
There has been an error in the blocks above
Run the app again or contact app’s creator
tabla_final = tabla_final.round(2)
There has been an error in the blocks above
Run the app again or contact app’s creator
tabla_final.to_excel(f'C:/Users/Juan.Zuluaga/Documents/Conciliacion retencion MX/Automatizacion/conciliaciones/conciliacion_{mes}.xlsx')
There has been an error in the blocks above
Run the app again or contact app’s creator