Notes

def payment_performance():

def extract_id():

date = dt.date.today()

query = f"""
select
applications.id,
farmer_payment_status,
farmer_last_collection_date,
farmer_payment_protest_date,
farmer_payment_date,
applications.decision,
applications.status,
orders.forward_price
from applications
left join retailers on retailers.id = applications.retailer_id
left join orders on orders.application_id = applications.id
left join findeks_requests on findeks_requests.application_id = applications.id
where
is_demo = 0
and sales_channel_id in (1,3,6)
and applications.status in (4)
and orders.deleted_at is null
and farmer_payment_date <= '{date}'
"""

df = ConnectMySQL("tarfin").direct_query(query)

df.farmer_last_collection_date = df.farmer_last_collection_date.fillna(date)
df.farmer_last_collection_date = pd.to_datetime(df.farmer_last_collection_date)
df.farmer_payment_protest_date = pd.to_datetime(df.farmer_payment_protest_date)
df["gecikme"] = (df.farmer_last_collection_date - df.farmer_payment_protest_date).dt.days
df.loc[(df.farmer_last_collection_date <= df.farmer_payment_protest_date) &
(df.farmer_last_collection_date >= df.farmer_payment_date) &
(df.farmer_payment_status==3),
"gecikme"] = 0
df.loc[df.gecikme >= 1, "gecikme"] = df.loc[df.gecikme >= 1, "gecikme"] + 2

return df

df = extract_id()
df = df.drop_duplicates()
df = df.set_index('id')

drop_ids = df[(df.gecikme <= 0) & (df.farmer_payment_status.isin([1,2]))].index.to_list()

df = df.drop(drop_ids)

df['payment_status'] = 'No'

df.loc[(df.farmer_payment_status == 3) & (df.gecikme < 15), 'payment_status'] = 'paid_on_time'
df.loc[(df.farmer_payment_status.isin([1,2])) & (df.gecikme > 0) & (df.gecikme < 15), 'payment_status'] = '0-15_open'
df.loc[(df.farmer_payment_status == 3) & (df.gecikme >= 15) & (df.gecikme < 90), 'payment_status'] = '15_90_paid'
df.loc[(df.farmer_payment_status.isin([1,2])) & (df.gecikme >= 15) & (df.gecikme < 90), 'payment_status'] = '15_90_open'
df.loc[(df.gecikme >= 90), 'payment_status'] = '90+'

return df