rtdc_compare

        from django.forms import model_to_dict
        npp = []
        dfp = pd.read_csv('/opt/fami/dbfaco/acodaypp.csv',
                            sep='|',
                            quotechar='"',
                            decimal=',',
                            encoding='utf-8',
                            dtype={'CAMPO14': str, 'CAMPO15': str, 'prod_gcas': str}
                        )
        dfp['FEC'] = pd.to_datetime(dfp['FEC'], format='%d/%m/%Y')
        years = dfp['FEC'].dt.year
        months = dfp['FEC'].dt.month
        dfp['YEAR'] = dfp['FEC'].dt.year
        dfp['MONTH'] = dfp['FEC'].dt.month
        dfp['MSU'] = dfp['STA'] / 1000

        year = 2023
        month = 4

        eeans = list(ExclussionTransaction.objects.using('rtdcbits')\
                    .filter(date__gte='{}-{}-01'.format(year, month))\
                    .values_list('dun', flat=True)\
                    .distinct('dun'))
        rtdcs = [ model_to_dict(a) for a in Fac.objects.using('rtdcbits').filter(
                                                            ~Q(product_dun14_code__in=eeans),
                                                            ~Q(canal='CONSUMO/INST'),
                                                            transaction_period__year=year,
                                                            transaction_period__month=month,
                                                            proveedor='PROCTER_AND_GAMBLE',
                                                            origen='ACO').exclude(familia='GENERAL', )
                                                            ]



        dfr = pd.DataFrame(rtdcs)

        #read the txt file
        pps = {'sep':'!', 'quotechar':'"', 'encoding':'utf-8'}
        dfexternal = pd.read_csv('/opt/external/LADMAR/fact_ACONCAGUA_{}012023.txt'.format(str(month).zfill(2)), converters={'store_code': str, 'store_salesrep_code': str }, **pps)
        dfexternal['indirect_shipments_customer_units'] = dfexternal['indirect_shipments_customer_units'].astype(float)
        dfexternal['store_name'] = dfexternal['store_name'].astype(str)
        dfexternal['transaction_period'] = dfexternal['transaction_period'].apply(lambda x: x.split('-')[0])
        dfexternal['transaction_period'] = pd.to_datetime(dfexternal['transaction_period'], format='%m/%d/%Y')
        dfexternal = dfexternal[dfexternal['transaction_type'].isin(['fac', 'dev'])].reset_index()
        mark_nc = dfexternal['transaction_type'] == 'dev'
        dfexternal.loc[mark_nc, 'indirect_shipments_amount'] = dfexternal[mark_nc].indirect_shipments_amount * -1
        dfexternal.loc[mark_nc, 'indirect_shipments_customer_units'] = dfexternal[mark_nc].indirect_shipments_customer_units * -1
        dfexternal.loc[mark_nc, 'indirect_shipments_pu'] = dfexternal[mark_nc].indirect_shipments_pu * -1

        dfexternal.rename(columns={'indirect_shipments_amount':'VTA', 'indirect_shipments_pu':'CAJ_PROVIDER', 'msu': 'MSU'}, inplace=True)

        rmark = dfr['transaction_type'].isin(['dev', 'fac'])
        dfr['indirect_shipments_amount'] = dfr['indirect_shipments_amount'].astype(float)
        dfr['indirect_shipments_pu'] = dfr['indirect_shipments_pu'].astype(float)
        dfr['msu'] = dfr['msu'].astype(float)
        dfr.rename(columns={'indirect_shipments_amount':'VTA', 'indirect_shipments_pu':'CAJ_PROVIDER', 'msu': 'MSU'}, inplace=True)
        pmark = (dfp['YEAR'] == year) & (dfp['MONTH'] == month) & (dfp['proveedor'] == 'PROCTER_AND_GAMBLE')
        a =  dfp[pmark][['familia_pg', 'VTA', 'CAJ_PROVIDER', 'MSU']].groupby(['familia_pg']).agg('sum').sort_values(by=['VTA'], ascending=False).reset_index()
        b = dfr[rmark][['familia_pg', 'VTA', 'CAJ_PROVIDER', 'MSU']].groupby(['familia_pg']).agg('sum').sort_values(by=['VTA'], ascending=False).reset_index()
        c = pd.merge(a, b, on='familia_pg', suffixes=('_CUBO', '_DB'))

        c['VTA'] = (c['VTA_CUBO']*100)/c['VTA_DB']
        c['CAJ_PROVIDER'] = (c['CAJ_PROVIDER_CUBO']*100)/c['CAJ_PROVIDER_DB']
        c['MSU'] = (c['MSU_CUBO']*100)/c['MSU_DB']
        c['YEAR'] = year
        c['MONTH'] = month
        c = c[['YEAR', 'MONTH', 'VTA_CUBO', 'VTA_DB', 'CAJ_PROVIDER_CUBO', 'CAJ_PROVIDER_DB', 'MSU_CUBO', 'MSU_DB', 'VTA', 'CAJ_PROVIDER', 'MSU']]
        print('Comparasion PPLAY vs Fac Model')
        print(c)
        print('#'*40)
        print('\n')
        #Compare against the .txt file

        dfr['prod_codigoviejo'] = dfr['product_customer_code'].astype(int)
        df_familia_pg = dict(dfr[['prod_codigoviejo', 'familia_pg']].to_dict(orient='split').get('data'))
        dfexternal['familia_pg'] = dfexternal['product_customer_code'].map(df_familia_pg).fillna('ND')
        rmark = dfexternal['transaction_type'].isin(['dev', 'fac'])
        b = dfexternal[rmark][['familia_pg', 'VTA', 'CAJ_PROVIDER', 'MSU']].groupby(['familia_pg']).agg('sum').sort_values(by=['VTA'], ascending=False).reset_index()
        ctxt = pd.merge(a, b, on='familia_pg', suffixes=('_CUBO', '_DB'))

        ctxt['VTA'] = (c['VTA_CUBO']*100)/c['VTA_DB']
        ctxt['CAJ_PROVIDER'] = (c['CAJ_PROVIDER_CUBO']*100)/c['CAJ_PROVIDER_DB']
        ctxt['MSU'] = (c['MSU_CUBO']*100)/c['MSU_DB']
        ctxt['YEAR'] = year
        ctxt['MONTH'] = month
        ctxt = ctxt[['YEAR', 'MONTH', 'VTA_CUBO', 'VTA_DB', 'CAJ_PROVIDER_CUBO', 'CAJ_PROVIDER_DB', 'MSU_CUBO', 'MSU_DB', 'VTA', 'CAJ_PROVIDER', 'MSU']]
        print('Comparasion PPLAY vs Txt File')
        print(ctxt)
        print('#'*40)
        print('\n')


        #Compare by product code
        dfr['prod_codigoviejo'] = dfr['product_customer_code'].astype(int)

        pmark = (dfp['YEAR'] == year) & (dfp['MONTH'] == month) & (dfp['proveedor'] == 'PROCTER_AND_GAMBLE')
        a =  dfp[pmark][['prod_codigoviejo', 'VTA', 'CAJ_PROVIDER', 'MSU']].groupby(['prod_codigoviejo']).agg('sum').sort_values(by=['VTA'], ascending=False).reset_index()
        b = dfr[rmark][['prod_codigoviejo', 'VTA', 'CAJ_PROVIDER', 'MSU']].groupby(['prod_codigoviejo']).agg('sum').sort_values(by=['VTA'], ascending=False).reset_index()
        dc = pd.merge(a, b, on='prod_codigoviejo', suffixes=('_CUBO', '_DB'))

        dc['VTA'] = (dc['VTA_CUBO']*100)/dc['VTA_DB']
        dc['CAJ_PROVIDER'] = (dc['CAJ_PROVIDER_CUBO']*100)/dc['CAJ_PROVIDER_DB']
        dc['MSU'] = (dc['MSU_CUBO']*100)/dc['MSU_DB']
        dc['YEAR'] = year
        dc['MONTH'] = month
        dc = dc[['prod_codigoviejo', 'YEAR', 'MONTH', 'VTA_CUBO', 'VTA_DB', 'CAJ_PROVIDER_CUBO', 'CAJ_PROVIDER_DB', 'MSU_CUBO', 'MSU_DB', 'VTA', 'CAJ_PROVIDER', 'MSU']]

        #compare by product against the .txt file
        dfexternal['prod_codigoviejo'] = dfexternal['product_customer_code'].astype(int)

        pmark = (dfp['YEAR'] == year) & (dfp['MONTH'] == month) & (dfp['proveedor'] == 'PROCTER_AND_GAMBLE')
        a =  dfp[pmark][['prod_codigoviejo', 'VTA', 'CAJ_PROVIDER', 'MSU']].groupby(['prod_codigoviejo']).agg('sum').sort_values(by=['VTA'], ascending=False).reset_index()
        rmark = dfexternal['transaction_type'].isin(['dev', 'fac'])
        b = dfexternal[rmark][['prod_codigoviejo', 'VTA', 'CAJ_PROVIDER', 'MSU']].groupby(['prod_codigoviejo']).agg('sum').sort_values(by=['VTA'], ascending=False).reset_index()
        dc = pd.merge(a, b, on='prod_codigoviejo', suffixes=('_CUBO', '_DB'))

        dc['VTA'] = (dc['VTA_CUBO']*100)/dc['VTA_DB']
        dc['CAJ_PROVIDER'] = (dc['CAJ_PROVIDER_CUBO']*100)/dc['CAJ_PROVIDER_DB']
        dc['MSU'] = (dc['MSU_CUBO']*100)/dc['MSU_DB']
        dc['YEAR'] = year
        dc['MONTH'] = month
        dc = dc[['prod_codigoviejo', 'YEAR', 'MONTH', 'VTA_CUBO', 'VTA_DB', 'CAJ_PROVIDER_CUBO', 'CAJ_PROVIDER_DB', 'MSU_CUBO', 'MSU_DB', 'VTA', 'CAJ_PROVIDER', 'MSU']]
        #See transaction per transaction day
        dfexternal['FEC'] = dfexternal['transaction_period']
        ta =  dfp[pmark][['FEC','prod_codigoviejo', 'VTA', 'CAJ_PROVIDER', 'MSU']].groupby(['FEC', 'prod_codigoviejo']).agg('sum').sort_values(by=['FEC'], ascending=False).reset_index()
        tb = dfexternal[rmark][['FEC','prod_codigoviejo', 'VTA', 'CAJ_PROVIDER', 'MSU']].groupby(['FEC','prod_codigoviejo']).agg('sum').sort_values(by=['FEC'], ascending=False).reset_index()
        tdc = pd.merge(ta, tb, on=['FEC','prod_codigoviejo'], suffixes=('_CUBO', '_DB'))
        tdc['VTA'] = (tdc['VTA_CUBO']*100)/tdc['VTA_DB']
        tdc['CAJ_PROVIDER'] = (tdc['CAJ_PROVIDER_CUBO']*100)/tdc['CAJ_PROVIDER_DB']
        tdc['MSU'] = (tdc['MSU_CUBO']*100)/tdc['MSU_DB']
        tdc['YEAR'] = year
        tdc['MONTH'] = month