Skip to content

Instantly share code, notes, and snippets.

@zsy715

zsy715/erp_investigation.md

Last active September 23, 2020 16:56
Show Gist options
  • Save zsy715/f2c47dd43a97f2767c899d97cfc7ac15 to your computer and use it in GitHub Desktop.
Save zsy715/f2c47dd43a97f2767c899d97cfc7ac15 to your computer and use it in GitHub Desktop.
Download ZIP
erp scrips investigation
Raw
erp_investigation.md

Outbound files

JDE -> OHL ✅:

  • */15 1,3,11,13,15,17,19,21,23 * * * cd $ERP_BASEDIR ; python jde_get.py
  • send purchase order to warehouse.
  • cron job is set up on app2/stu
  • cron job triggers jde_get.py: get csv files(with prefix 943) from smb server PD910/outbound
  • call etl_pass.py: persists data into ohl_resp database, triggers 943.py
  • call 943.py: convert original csv to tsv(with prefix BBXITPO), post files to ohl ftp
  • files(BBXITPO) would potentially get lost if ftp upload fails(archive happens after ftp upload). See https://github.com/birchbox/erp_integration/blob/develop/ETL/943.py#L131-L134
upload_to_ftp(file = df_data_dict['out_file_name'], config = ftp_config)

archive_file(df_data_dict['out_file_name'], LOCAL_DST_DIR_PATH_DEFAULT)
archive_file(df_data_dict['err_file_name'], LOCAL_DST_DIR_PATH_DEFAULT)
  • All files(943 and BBXITPO) are archived in /home/stu/Dropbox/dotcom_data/archive folder

JDE -> CHR ✅:

  • send purchase order to CHR
  • cron job is set up in a docker container due to its special need of mssql db driver.
  • container currently is on bold-supply and log is located in /srv/TOPS-1871/archive/purchase_order_post.log
  • cron triggers ./CHR/purchase_order_post.py: since is from last run, until is now
  • script connects mssql to fetch po information
  • script uploads files(eg CHR_336656_20200914203326.XML) to CHR ftp
  • script archives CHR files to folder /srv/TOPS-1871/archive/archive/

JDE -> IQ ✅:

  • 30 2 * * * cd $ERP_BASEDIR ; cd IQ ; python jde_to_iq.py 2>&1 | mail -Es 'Output of jde_to_iq.py' erp-cron@birchbox.com
  • cron job triggers jde_to_iq.py
  • script gets files(prefix BBXExtract*.csv eg BBXExtract_202009140430.csv) from smb server PD910/outbound/DIQ
  • then copy files to app2 folder /user/local/erp_integration/temp
  • post files to iq sftp server
  • archive files to app2 folder /usr/local/dotcom/Dropbox/dotcom_data/archive/
  • archive files to smb archive folder PD910/archive/outbound/DIQ

JDE -> BOA ✅:

  • 5 17 * * 1,2,3,4,5 cd $ERP_BASEDIR ; cd BOA ; python jde_to_boa.py 2>&1 | mail -Es 'Output of jde_to_boa.py' erp-cron@birchbox.com
  • cron job triggers jde_to_boa.py
  • script gets files(:question:) from smb server PD910/outbound/Cashpro
  • then copy files to app2 folder /user/local/erp_integration/temp
  • post files to boa sftp server
  • archive files to app2 folder /usr/local/dotcom/Dropbox/dotcom_data/archive/
  • archive files to smb archive folder PD910/archive/outbound/Cashpro

Questions to Annissa: :question:How files are dropped to smb outbound folder?

❓What does the name of files uploaded to BOA look like?

❓Is there an estimation of down time?

Inbound files

re_smb.py:

  • get edi based on file name such as 945, 947, 944, RI
  • get smb post folder by
def get_smb_postdir(edi, spec, smb_config):
    dir = smb_config['smb_post_dir']
    subdir = spec["EDI"]["SMB_POST_SUBDIRECTORY"][edi]
    return os.path.join(dir, subdir)
  • archive files to local folder /user/local/dotcom/Dropbox/dotcom_data/archive

item_information.py

  • */15 0-3,11-23 * * * cd $ERP_BASEDIR ; python item_information.py -t 888,415,410,410_upc 2>&1
  • this script fetch pim info and magento info
  • it retrieves data in table erp_item_queue in tools server
  • for 410
  • upload files(prefix 410) to smb folder PD910/inbound/D410
  • archive files to local folder on app2 /user/local/dotcom/Dropbox/dotcom_data/archive.
  • 415 is same as 410
  • upload files(prefix 415) to smb folder PD910/inbound/D415
  • 888 is same as 410
  • upload files(prefix 888) to smb folder PD910/inbound/D888
  • 410_upc is same as 410
  • test with dev db config
  • python item_information.py -t 410,415,888,410_upc
  • failed to upload would leave 410 files on the same folder where the script lives
  • fail to upload smb doesn't lose data. They would be included in next successful cron.

947.py(947 is ADJ):white_check_mark::

  • script is called by etl_pass.py
  • upload to smb folder PD910/inbound/D947
  • archive file to local folder on app2 /user/local/dotcom/Dropbox/dotcom_data/archive
  • files are saved in to_smb if smb upload fails.

944.py(944 is RC):white_check_mark::

  • script is called by etl_pass.py.
  • get po information from mssql.
  • upload to smb folder PD910/inbound/D944.
  • archive file to local folder on app2 /user/local/dotcom/Dropbox/dotcom_data/archive.
  • files are saved in to_smb if smb upload fails.

shipment_945.py(945 is CSH):white_check_mark::

  • script is called by etl_pass.py.
  • upload to smb folder based on different channels and sources (eg, OSM york site, chicago site, vegas site, 11,96,97,98,99)
"945" : "D945"
"945_96": "D945Consign_96"
"945_97": "D945Consign_97"
"945_98": "D945Tester_98"
"945_99": "D945Web_99"
"945_11": "D945_11"
"945_CHI": "D945_OSM"
"945_VEG": "D945_OSM"
"945_YRK": "D945_OSM"
"945_OSM": "D945_OSM"
  • archive file to local folder on app2 /user/local/dotcom/Dropbox/dotcom_data/archive.
  • files are saved in to_smb if smb upload fails.

iq_to_jde.py

  • 0 1 * * * cd $ERP_BASEDIR ; cd IQ ; python iq_to_jde.py 2>&1 | mail -Es 'Output of iq_to_jde.py' erp-cron@birchbox.com
  • retreive files from iq sftp
  • files are saved to ../temp folder
  • upload files to smb PD910/inbound/DIQ
  • upload files to smb archive folder PD910/archive/inbound/DIQ
  • archive(rename) file (prefix BBXReturn ❓) to local folder on app2 /user/local/dotcom/Dropbox/dotcom_data/archive.

litle_get.py

  • 0 */4 * * * cd $ERP_BASEDIR/litle ; python litle_get.py 2>&1 | mail -s 'Output of litle_get.py' erp-cron@birchbox.com
  • python litle_get.py
  • retrieves files from litle sftp
  • files would be splitted into 3 types ("Refunds", "Amex US File", "Litle File")
  • scp separated files to folder litle-reports-updated on file.team.birchbox.com
  • archive files to local folder /usr/local/dotcom/archive/
  • scp files to file.team.birchbox.com
  • upload files except Refund files to smb PD910/inbound/Litle
  • file name examples
[prod] shengyang@app2.nyc2: /usr/local/dotcom/archive/2020/09/14 $ ls *Litle*
20200914 Litle File.csv
[prod] shengyang@app2.nyc2: /usr/local/dotcom/archive/2020/09/14 $ ls *Amex*
20200914 Amex US File.csv
[prod] shengyang@app2.nyc2: /usr/local/dotcom/archive/2020/09/14 $ ls *Refund*
20200914 Refunds.csv
[prod] shengyang@app2.nyc2: /usr/local/dotcom/archive/2020/09/14 $
  • files are supposed be saved to litle/to_SMB folder but they are not regarding to current exception handling
  • log file is in log/litle_log.csv

assortment_information.py

  • */15 6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22 * * * cd $ERP_BASEDIR ; python assortment_information.py -t 832 2>&1 | mail -s 'assortment_information' kokoro@birchbox.com
  • retrieves pim assortment information
  • upload files to smb PD910/inbound/D832
  • remove data from erp_assortment_queue
  • add data in erp_assortment_sent

RI-sales.py

  • 0 9 * * * $ERP_BASEDIR/scripts/RI-sales_mailer_us.sh erp-cron@birchbox.com
  • upload files to smb PD910/inbound/DRI
  • log in log/RI-sales_log.csv
  • test with staging db config
  • python RI_sales.py -f
  • failed to upload would leave RI files on the same folder where the script lives

RI-rebill.py

  • 30 9 * * * $ERP_BASEDIR/scripts/RI-rebill_mailer_us.sh erp-cron@birchbox.com
  • upload files to smb PD910/inbound/DRI
  • test with staging db config
  • python RI_rebill.py -g -r
  • failed to upload would leave RI files on the same folder where the script lives

item_queue.py

  • */15 0-3,11-23 * * * cd $ERP_BASEDIR ; python item_queue.py 2>&1

etl_pass.py

  • python etl_pass.py file_path

summary

  • 944.py/947.py/shipments_945.py:

    • S-> inbound/D945
    • smb_F-> to_SMB Pick by re_smb.py S-> post to inbound/D945 && archive to archive/2020/09/11/to_SMB
    • 947_mssql_F -> no file would be generated -> need to retrigger 947.py

  • need to monitor birchboxx_lastrun table to make sure there is no script update for

RI_sales.py, RI_rebill.py, 410.py, 415.py, 410_upc.py, 888.py, 944.py
  • iq to jde files would stay in ../temp folder
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment