{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import snowflake.connector\n",
    "import os\n",
    "import pandas as pd\n",
    "import csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def connect_to_snowflake():\n",
    "    \n",
    "    credentials = snowflake.connector.connect(\n",
    "        user = os.getenv('SNOWFLAKE_USER'),\n",
    "        password = os.getenv('SNOWFLAKE_PASSWORD'),\n",
    "        account = 'orchard',\n",
    "        warehouse = os.get_env('SNOWFLAKE_WAREHOUSE')\n",
    "    )\n",
    "    \n",
    "    return credentials.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_date():\n",
    "    \n",
    "    cursor = connect_to_snowflake()\n",
    "    \n",
    "    sql = '''\n",
    "\n",
    "    SELECT\n",
    "        DATE_FROM_PARTS(year,month,31) :: string AS date\n",
    "    FROM facts.prod.dim_period\n",
    "    WHERE periodid=248\n",
    "\n",
    "    '''\n",
    "\n",
    "    results = cursor.execute(sql)\n",
    "\n",
    "    df = pd.DataFrame(results, columns=['date'])\n",
    "\n",
    "    df['date'] = df['date'].str.replace('-','')\n",
    "\n",
    "    return df['date'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "def generate_sap_upload():\n",
    "    \n",
    "    date = get_date ()\n",
    "    \n",
    "    cursor = connect_to_snowflake()\n",
    "    \n",
    "    with open('row1.sql','r') as f:\n",
    "        sql = f.read() \n",
    "        \n",
    "    results = cursor.execute(sql).fetchall()\n",
    "    \n",
    "    df = pd.DataFrame(results)\n",
    "    \n",
    "    df.to_csv('mechanicals_row1.csv', index=False)\n",
    "    \n",
    "    with open('row2.sql','r') as f:\n",
    "        sql = f.read()\n",
    "\n",
    "    results = cursor.execute(sql).fetchall()\n",
    "\n",
    "    df = pd.DataFrame(results)\n",
    "    df.to_csv('mechanicals_row2.csv', index=False)\n",
    "    \n",
    "    with open('header.sql','r') as f:\n",
    "        sql = f.read()\n",
    "        sql = sql.format(date=date)\n",
    "\n",
    "    results = cursor.execute(sql).fetchall()\n",
    "\n",
    "    df= pd.DataFrame(results)\n",
    "    df.to_csv('mechanicals_header.csv', index=False)\n",
    "    \n",
    "    with open('mechanicals_header.csv','r') as h:\n",
    "        with open('mechanicals_row1.csv','r') as r1:\n",
    "            with open('mechanicals_row2.csv','r') as r2:\n",
    "            \n",
    "                        \n",
    "                headers=list(csv.reader(h))\n",
    "                row1=list(csv.reader(r1))\n",
    "                row2=list(csv.reader(r2))\n",
    "\n",
    "                with open('phonofile_upload.csv','w') as s:\n",
    "                    sap = csv.writer(s, delimiter=',')\n",
    "\n",
    "                    index=1\n",
    "\n",
    "                    for row in headers[1:]:\n",
    "                        sap.writerow(headers[index])\n",
    "                        sap.writerow(row1[index])\n",
    "                        sap.writerow(row2[index])\n",
    "                        index+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "generate_sap_upload()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"WITH mechanicals AS (\\nSELECT\\n\\tfs.labelid,\\n    fs.accountingyear,\\n    fs.accountingmonth,\\n\\tCOALESCE(SUM((fs.fx_dpd_publishing + fs.fx_oms_fees + fs.fx_ringtone_publishing + fs.fx_cloud_publishing) ), 0) AS mechanicals\\nFROM facts.prod.fact_sales fs\\n    LEFT JOIN facts.prod.DIM_TRANSACTIONTYPE  dtt ON fs.TRANSACTIONTYPEID=dtt.TRANSACTIONTYPEID\\n\\n\\nWHERE (fs.ACCOUNTINGPERIODID  = (SELECT MAX(accountingperiodid) FROM facts.prod.fact_sales)) AND ((dtt.TRANSACTIONTYPEABBR  NOT IN ('PS', 'RE') OR dtt.TRANSACTIONTYPEABBR IS NULL))\\nGROUP BY 1,2,3\\n),  row1 AS (\\n\\nSELECT 'BPOS' AS  REC_TYPE,\\n'1' AS  ITEM_NUM,\\n'H' AS  DRCR_IND,\\n'2986' AS  COMP_CODE,\\n'S' AS  ACC_TYPE,\\n'2187000002' AS  ACCOUNT,\\nm.labelid||'|'||UPPER(monthname(date_from_parts('1993',m.accountingmonth,'01')))||RIGHT(m.accountingyear,2) AS ASSIGNMENT,\\n'Mechanicals '||m.labelid||'|'||UPPER(monthname(date_from_parts('1993',m.accountingmonth,'01')))||RIGHT(m.accountingyear,2) AS ITEM_TXT,\\n'' AS  BLINE_DATE,\\n'' AS  SPL_GL_INDICATOR,\\n'' AS  TRADE_PART,\\n'' AS  TAX_CODE,\\n'' AS  TAX_TYPE,\\n'' AS  COND_REC,\\n'' AS  COND_TYPE,\\nABS(m.mechanicals) AS amount,\\n'' AS  DMBTR,\\n'' AS  TAXAMT,\\n'' AS  MWSTS,\\n'' AS  TAX_BASEAMT,\\n'' AS  DISC_AMT,\\n'' AS  SKNTO,\\n'' AS  FORCURR_BASEDIS,\\n'' AS  PAYMENT_TERMS,\\n'' AS  COST_CENTER,\\n'' AS  INT_ORDER,\\n'' AS  WBS_ELEM,\\npc.profitcenter AS  PROFIT_CNTR,\\n'' AS  MOVE_TYPE,\\n'' AS  CHANNEL ,\\n'' AS  PRODUCT,\\n'' AS  Customer,\\n'' AS  Vendor,\\n'' AS  Units,\\n'' AS  Report_Date_to,\\n'' AS  Report_Date_from,\\n'' AS  Unit_Price,\\n'' AS  SAMIS_Channel,\\n'' AS  SAMIS_Product_type,\\n'' AS  BW_Reference1,\\n'' AS  BW_Reference2,\\n'' AS  INV_Category,\\n'' AS  Pay_Method,\\n'' AS  REF_KEY1,\\n'' AS  REF_KEY2,\\n'' AS  REF_KEY3,\\n'' AS  WTH_EXM_AMT,\\n'' AS  REASON_CODE,\\n'' AS  LONG_TEXT,\\n'' AS  TXJCD\\nFROM mechanicals m\\nLEFT JOIN intelligence.dev_akhoudary.phonofile_profit_center_mappings pc ON pc.labelid = m.labelid\\nWHERE m.mechanicals < 0) SELECT * FROM row1 WHERE PROFIT_CNTR IS NOT NULL\\nORDER BY ITEM_TXT;\\n\""
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql.format(pc='phonofile_profit_center_mappings')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}