sync_amz_data/start_sync_amz.py

import time

from sync_amz_data.settings import LOG_CONF
import logging.config
logging.config.dictConfig(LOG_CONF)
from sync_amz_data.public.amz_ad_client import shop_infos
from sync_amz_data.DataTransform.Data_ETL import Common_ETLMethod,SP_ETL,SB_ETL,SD_ETL
from apscheduler.schedulers.blocking import BlockingScheduler
from datetime import datetime,timedelta

def amz_report(AWS_CREDENTIALS,para=None):
    count = 1
    list_date = [((datetime.today()+timedelta(days=i)).date()).isoformat() for i in range(-2,-10,-1)]
    conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect()
    # conn.command('')
    if para is not None:
        list_date = [para['startDate']]
    # print(list_date)
    for date_ in list_date:
        print(date_)
        print(date_.replace("-", ""))
        # amz_report(conn, AWS_CREDENTIALS, para={"startDate": date_, "endDate": date_, "date": date_.replace("-", "")})
        if count==1:
            delete_list = ['SB_adGroupsV2',
                            'SB_adGroupsVideoV2',
                            'SB_adsV2',
                            'SB_adsVideoV2',
                            'SB_campaignsPlacementV2',
                            'SB_campaignsPlacementVideoV2',
                            'SB_campaignsV2',
                            'SB_campaignsVideoV2',
                            'SB_keywordsQueryV2',
                            'SB_keywordsQueryVideoV2',
                            'SB_keywordsV2',
                            'SB_keywordsVideoV2',
                            'SB_sbAdGroup_adGroupV3',
                            'SB_sbAds_adsV3',
                            'SB_sbCampaigns_campaignV3',
                            'SB_sbCampaigns_placementV3',
                            'SB_sbPurchasedProduct_asinV3',
                            'SB_sbSearchTerm_searchTermV3',
                            'SB_sbTargeting_targetingV3',
                            'SB_targetsV2',
                            'SB_targetsVideoV2',
                            'SD_adGroupsMatchedTargetV2',
                            'SD_adGroupsV2',
                            'SD_adsV2',
                            'SD_advertiser_sdAdvertisedProductV3',
                            'SD_asin_sdPurchasedProductV3',
                            'SD_asinsV2',
                            'SD_campaignsMatchedTargetV2',
                            'SD_campaignsV2',
                            'SD_sdAdGroup_adGroupMatchedTargetV3',
                            'SD_sdAdGroup_adGroupV3',
                            'SD_sdCampaigns_campaignMatchedTargetV3',
                            'SD_sdCampaigns_campaignV3',
                            'SD_targeting_sdTargetingMatchedTargetV3',
                            'SD_targeting_sdTargetingV3',
                            'SD_targetsMatchedTargetV2',
                            'SD_targetsV2',
                            'SP_spAdvertisedProduct_advertiserV3',
                            'SP_spCampaigns_adGroupV3',
                            'SP_spCampaigns_campaignV3',
                            'SP_spCampaigns_placementV3',
                            'SP_spPurchasedProduct_asinV3',
                            'SP_spSearchTerm_searchTermV3',
                            'SP_spTargeting_targetingV3']
            for i in delete_list:
                conn.command(f"delete from AmazonReport.{i} where date='{list_date[-2]}'")
            count+=1

        try:
            refresh_token = shop_infos(AWS_CREDENTIALS['profile_id'])['refresh_token']
            AWS_CREDENTIALS['refresh_token'] = refresh_token
        except Exception as e:
            print(e)

        conn = SB_ETL(**AWS_CREDENTIALS).clickhouse_connect()
        sb_report = SB_ETL(**AWS_CREDENTIALS)
        sb_report.reportV3_campaign_sbCampaigns_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV3_adGroup_sbAdGroup_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV3_sbCampaignPlacement_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV3_sbTargeting_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV3_sbSearchTerm_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV3_sbAds_ETL(conn, params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV3_purchasedAsinRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})

        sb_report.reportV3_purchasedAsinRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_campaignsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_campaignsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_adGroupsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_adGroupsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_adsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_adsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_keywordsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_keywordsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_placementRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_placementVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_searchtermsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_searchtermsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_targetsRecord_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sb_report.reportV2_targetsVideo_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        conn.close()
        time.sleep(5)

        conn = SP_ETL(**AWS_CREDENTIALS).clickhouse_connect()
        sp_report = SP_ETL(**AWS_CREDENTIALS)
        sp_report.reportV3_campaign_spCampaignsETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sp_report.reportV3_adGroup_spCampaignsETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sp_report.reportV3_campaignPlacement_spCampaignsETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sp_report.reportV3_targeting_spTargetingETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sp_report.reportV3_searchTerm_spSearchTermETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sp_report.reportV3_advertiser_spAdvertisedProductETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sp_report.reportV3_asin_spPurchasedProductETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        conn.close()
        time.sleep(5)


        conn = SD_ETL(**AWS_CREDENTIALS).clickhouse_connect()
        sd_report = SD_ETL(**AWS_CREDENTIALS)

        sd_report.reportV3_campaign_sdCampaigns_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sd_report.reportV3_campaignMT_sdCampaigns_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sd_report.reportV3_adgroup_sdAdGroup_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sd_report.reportV3_adgroupMT_sdAdGroup_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sd_report.reportV3_targeting_sdTargeting_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sd_report.reportV3_targetingMT_sdTargeting_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sd_report.reportV3_asin_sdPurchasedProduct_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})
        sd_report.reportV3_advertiser_sdAdvertisedProduct_ETL(conn,params={"startDate":date_,"endDate":date_,"date":date_.replace("-","")})

        # sd_report.reportV2_campaignsRecord_t2_ETL(conn,params=para)
        # sd_report.reportV2_campaignsRecord_t3_ETL(conn,params=para)
        # sd_report.reportV2_adGroupsRecord_t2_ETL(conn,params=para)
        # sd_report.reportV2_adGroupsRecord_t3_ETL(conn,params=para)
        # sd_report.reportV2_asins_t2_ETL(conn,params=para)
        # sd_report.reportV2_asins_t3_ETL(conn,params=para)
        # sd_report.reportV2_productAds_t2_ETL(conn,params=para)
        # sd_report.reportV2_productAds_t3_ETL(conn,params=para)
        # sd_report.reportV2_targets_t2_ETL(conn,params=para)
        # sd_report.reportV2_targets_t3_ETL(conn,params=para)
        # sd_report.reportV2_campaign_matchedTarget_t2_ETL(conn,params=para)
        # sd_report.reportV2_campaign_matchedTarget_t3_ETL(conn,params=para)
        # sd_report.reportV2_adGroups_matchedTarget_t2_ETL(conn,params=para)
        # sd_report.reportV2_adGroups_matchedTarget_t3_ETL(conn,params=para)
        # sd_report.reportV2_targets_matchedTarget_t2_ETL(conn,params=para)
        # sd_report.reportV2_targets_matchedTarget_t3_ETL(conn,params=para)
        conn.close()
        time.sleep(5)

if __name__ == '__main__':
    # AccountTask("3006125408623189").do({"record": "portfolios"})
    AWS_CREDENTIALS = {
        'lwa_client_id': 'amzn1.application-oa2-client.ebd701cd07854fb38c37ee49ec4ba109',
        'refresh_token': "Atzr|IwEBIL4ur8kbcwRyxVu_srprAAoTYzujnBvA6jU-0SMxkRgOhGjYJSUNGKvw24EQwJa1jG5RM76mQD2P22AKSq8qSD94LddoXGdKDO74eQVYl0RhuqOMFqdrEZpp1p4bIR6_N8VeSJDHr7UCuo8FiabkSHrkq7tsNvRP-yI-bnpQv4EayPBh7YwHVX3hYdRbhxaBvgJENgCuiEPb35Q2-Z6w6ujjiKUAK2VSbCFpENlEfcHNsjDeY7RCvFlwlCoHj1IeiNIaFTE9yXFu3aEWlExe3LzHv6PZyunEi88QJSXKSh56Um0e0eEg05rMv-VBM83cAqc5POmZnTP1vUdZO8fQv3NFLZ-xU6e1WQVxVPi5Cyqk4jYhGf1Y9t98N654y0tVvw74qNIsTrB-8bGS0Uhfe24oBEWmzObvBY3zhtT1d42myGUJv4pMTU6yPoS83zhPKm3LbUDEpBA1hvvc_09jHk7vUEAuFB-UAZzlht2C1yklzQ",
        'lwa_client_secret': 'cbf0514186db4df91e04a8905f0a91b605eae4201254ced879d8bb90df4b474d',
        'profile_id': "3006125408623189"
    }
    try:
        refresh_token = shop_infos(AWS_CREDENTIALS['profile_id'])['refresh_token']
        AWS_CREDENTIALS['refresh_token'] = refresh_token
    except Exception as e:
        print(e)

    timezone_ = Common_ETLMethod(**AWS_CREDENTIALS).timeZone()
    print(timezone_)
    # amz_report(AWS_CREDENTIALS)
    sched = BlockingScheduler()
    sched.add_job(amz_report,'cron',hour=17,minute=0,second=0,timezone=timezone_, args=(AWS_CREDENTIALS, ))#,params={"startDate":"2023-11-04","endDate":"2023-11-04","date":"20231104"}
    sched.start()