nlu_to_sql-Copy1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT sum(amount) FROM Statement WHERE customer_Id = 'C2' AND category = 'Gov Tax' AND date < '2020-07-09 00:00:00' AND date >= '2016-05-09 00:00:00'\n"
     ]
    }
   ],
   "source": [
    "#Importing the packages required\n",
    "import datetime\n",
    "import sqlite3 \n",
    "import re\n",
    "from dateutil.relativedelta import relativedelta\n",
    "import pandas as pd\n",
    "import matplotlib as mpl\n",
    "import sys\n",
    "%matplotlib inline\n",
    "\n",
    "#Inputs of the rasa_nlu models\n",
    "entities=['Gov Tax','50_months']\n",
    "intent=['how_much']\n",
    "customer='C2'\n",
    "def rearrange(list):\n",
    "    if list[0] in ['coffee','Gov Tax','Taxi','Food','Clothings']:\n",
    "        return list\n",
    "    if list[1] in ['coffee','Gov Tax','Taxi','Food','Clothings']:\n",
    "        temp=list[0]\n",
    "        list[0]=list[1]\n",
    "        list[1]=temp\n",
    "        return list\n",
    "entities=rearrange(entities)\n",
    "\n",
    "\n",
    "#Program for asking Intent\n",
    "if intent==[]:\n",
    "    intent.append('a')\n",
    "if intent[0] not in ['how_much','how_many']:\n",
    "    inp = \"\"\n",
    "    while inp != \"how_much spent\" and inp != \"how_many times spent\":\n",
    "        print('Hi',customer,'Hi,I didnot get what you are exactly looking for. ')\n",
    "        inp = input(\"Please,Enter what you require from: how_much spent or how_many times spent: \")\n",
    "        if inp != \"how_much spent\" and inp != \"how_many times spent\":\n",
    "            print(\"You must type how_much spent or how_many times spent\")\n",
    "    intent[0]=inp.split(' ')[0]\n",
    "if len(entities)!=2:\n",
    "    inp1 = \"\"\n",
    "    while inp1 != \"how_much spent\" and inp != \"how_many times spent\":\n",
    "        print('Hi',customer,'Hi,I didnot get what you are exactly looking for. ')\n",
    "        inp = input(\"Please,Enter what you require from: how_much spent or how_many times spent: \")\n",
    "        if inp1 != \"how_much spent\" and inp != \"how_many times spent\":\n",
    "            print(\"You must type how_much spent or how_many times spent\")\n",
    "#Function that transforms the list of entities into an sql query\n",
    "def query(list):\n",
    "    to = datetime.date.today()\n",
    "    if list[1].split('_')[1]=='days':\n",
    "        fro=to+relativedelta(days=-int(list[1].split('_')[0]))\n",
    "    if list[1].split('_')[1]=='months':\n",
    "        fro=to+relativedelta(months=-int(list[1].split('_')[0]))\n",
    "    if list[1].split('_')[1]=='years':\n",
    "        fro=to+relativedelta(years=-int(list[1].split('_')[0]))\n",
    "    to=to.strftime(\"%Y-%m-%d 00:00:00\")\n",
    "    fro=fro.strftime(\"%Y-%m-%d 00:00:00\")\n",
    "    item='a'\n",
    "    for i in list:\n",
    "        if i in ['coffee','Gov Tax','Taxi','Food','Clothings']:\n",
    "            item=i\n",
    "    if intent[0]=='how_much':\n",
    "        j='sum(amount)'\n",
    "    if intent[0]=='how_many':\n",
    "        j='COUNT(amount)'  \n",
    "    return 'SELECT ',j,' FROM Statement WHERE customer_Id = ',customer,' AND category = ',item, ' AND date < ',to,' AND date >= ',fro\n",
    "\n",
    "#Using the function query,that transforms the list of entities into an sql query\n",
    "x=[]\n",
    "for i in query(entities):\n",
    "    x.append(i)\n",
    "s= ''\n",
    "x=s.join(x)\n",
    "x\n",
    "c=query(entities)\n",
    "\n",
    "#Rearranging the SQL query in the required form\n",
    "\n",
    "s= ''\n",
    "d=[s.join(c[0:3]),c[3],c[4],c[5],c[6],c[7],c[8],c[9],\"\"]\n",
    "x=[]\n",
    "for i in d:\n",
    "    x.append(i)\n",
    "s= \"'\"\n",
    "x=s.join(x)\n",
    "\n",
    "#Print the SQL query that is used to query the Database corresponding to the given inputs from the Rasa_nlu model\n",
    "print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "276\n"
     ]
    }
   ],
   "source": [
    "# connect withe the myTable database \n",
    "connection = sqlite3.connect(\"statement.db\")\n",
    "\n",
    "#Function that takes the connection and outputs the table containg the result of the query\n",
    "def sum(conn):\n",
    "    \"\"\"\n",
    "    Query sum of amount in the statement table corresponding to the other entity constraints\n",
    "    :param conn: the Connection object\n",
    "    :return:\n",
    "    \"\"\"\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(x)\n",
    "    #cur.execute(\"SELECT sum(amount) FROM Sheet1 WHERE customer_Id='C1' AND category='Clothings' AND date <'2020-02-20 00:00:00' AND date>= '2019-12-20 00:00:00'\")\n",
    "    results=cur.fetchall()\n",
    "    return results\n",
    "\n",
    "#Function that takes the connection as input and outputs the result of the query\n",
    "def result(conn):\n",
    "    for i in sum(conn):\n",
    "        return i[0]\n",
    "r=result(connection)               #Store the result in r\n",
    "print(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hi C2 , The amount you spent in the last 50 months on Gov Tax is 276 dollars\n"
     ]
    }
   ],
   "source": [
    "if r==None:\n",
    "    print(\"Oops...! Couldn't find any transactions matching this criteria\")\n",
    "else:\n",
    "    #Output the result as a statement(in the format that is more of an interactive in nature)\n",
    "    if intent[0]=='how_much':\n",
    "        print('Hi',customer,\",\",'The amount you spent in the last',entities[1].split('_')[0],entities[1].split('_')[1],'on',entities[0],'is',r,'dollars')\n",
    "    if intent[0]=='how_many':\n",
    "        print('Hi',customer,\",\",'The no. of times you spent in the last',entities[1].split('_')[0],entities[1].split('_')[1],'on',entities[0],'is',r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT * FROM Statement WHERE customer_id = 'C2' AND category = 'Gov Tax' AND date < '2020-07-09 00:00:00' AND date >= '2016-05-09 00:00:00'\n",
      "Hi Mr C2 ,\n",
      "The amount you spent in the last 50 months on Gov Tax is 276 dollars\n",
      "        amount\n",
      "date          \n",
      "Aug-19      64\n",
      "Oct-19      87\n",
      "Nov-19      56\n",
      "Dec-19      69\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAW4AAADuCAYAAAAZZe3jAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi41LCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvSM8oowAAIABJREFUeJzt3Xl4G9W5P/DvmdFosyxb8r7FjhPbkhOyEBKzZoOwNQlr2FoSmrKmFEp/LCllK6W099KWQuklFAolUCBALxBIoKyXHUpCIE68ZLXjxPsmWbbWmfP7Y+TE2b1IGUl+P88zj+3RzOhVYr86OufMexjnHIQQQuKHoHUAhBBChoYSNyGExBlK3IQQEmcocRNCSJyhxE0IIXGGEjchhMQZStyEEBJnKHETQkicocRNCCFxhhI3IYTEGUrchBASZyhxE0JInKHETQghcYYSNyGExBmd1gEQQhLT+vXrM3U63VMAJoIaiQMpADaFQqGrp02b1jqcC1DiJoREhU6neyo7O9uZkZHRJQgCFf4PUxSFtbW1lTc3Nz8FYOFwrkHvgoSQaJmYkZHhpqS9P0EQeEZGhgvqJ5HhXSOC8RBCyEACJe1DC/+7DDv/UuImhJA4Q33chJBjomj5mmmRvF7d73+wfjDHPffcc6mLFy8e9+23326eOnWqLxLP/eCDD2asWLEiq6GhwdDY2Ph9Tk5OCADa2trEK664oqi+vt5gMBj4008/vXP69OkRec6BqMVNCEloL730kv3444/3rFy50h6pa86aNcvz3nvvbcnNzQ0M3H/XXXflTJo0qW/Lli1VK1eu3HnTTTeNidRzDkSJmxCSsFwul/DNN99YnnnmmbrXXnvNDgBvvfVW8pw5c8b3H7N48eIxjz76aBoArFq1KmXs2LETJkyY4LzqqqsKBh430CmnnOItKysLHLi/trbWOG/evB4AmDp1qm/37t36hoaGiPdsUOImhCSsF154IXX27NmuSZMm+W02W+jTTz81H+7Yvr4+dvPNNxe+/fbbWzdv3lzd0dEx5IQ7ceJE7yuvvGIDgI8++sjc1NRkqKur04/kNRwKJW5CSMJ6+eWX7ZdffnkXAFx00UWdzz333GG7S7777jtjQUGB3+FwBADgsssu6xzq891///1NLpdLdDgc5Y888kiWw+HoE0Ux4jNraHCSEJKQWlpaxK+++iq5trbWdOONN0KWZcYY4xdeeGG3oih7j/P7/exo1zr11FNL2tvbpcmTJ/euWrWq/nDH2e125dVXX60DAEVRUFBQcJzD4fBH4vUMRImbEJKQnnvuOdsFF1zQ+cILL+xNtNOnTy+TZRnbtm0zeb1e1tvbK3z22WfWU045xTNp0iRfQ0ODoba2Vl9WVhZYtWrV3tb5Z599tnUwz9ne3i5aLBbFaDTyhx9+OH3GjBk9drtdOfqZQ0OJmxByTAx2+l6kvPLKK/bbbruteeC+8847r+uFF16wL1iwoMvhcEzIz8/3T5gwoQ8ALBYL/9Of/lR/9tlnl5jNZmXy5Mm9h7v2Aw88kPmXv/wlu6OjQ5o8eXL5nDlzXKtWrar/7rvvjFdfffVYACgtLfX+85//rIvGa2Oc041NhJDI+/777+smT57crnUcQ+FyuYSUlBRFURQsXrx4TElJie/ee+8dViGoo/n+++/TJ0+eXDScc2lwkhBCwv785z+nOxyO8pKSkglut1v8xS9+EZNvPNTiJoRERTy2uI+lkbS4qY+bxL2i5WvMAFIBpIS3VADJUH+/BQBi/9fzt33ivW7TahMAObwpA773A3AB6Apv3c6a6p5j+2oIOTpK3CTmFC1fYwJQCGBM+GshgFyoCfnABG0FIA322laf+0sAJw32+GqHM4R9ybz7EF/bAdQB2Algh7OmeshzfwkZKkrc5JgLJ2YH9iXl/m0M57yQMZYRref2et1jh3iKDkBaeDuqaofTDTWJ7wSwY8D3OwHsdNZUe4f4/IQchBI3iaqi5WsKAEwOb5M455MBlDDGDjkwzthR74UYEUEQD6ovEWFW7Hu9B6l2OJsBbAHwbXhbD6DGWVMd8bm+JHFR4iYRUbR8jRHABISTFud8CoDjGGO2gcdFOzHHgezwNnPAvt5qh/N7qEm8P5lXOWuqZQ3ii577UiJa1hX3uQY1L3z79u3StddeO2bbtm0mRVFwxhlnuB5//PHdRqPxkDMz7r///sxbbrmlPTk5+aA30+bmZvG8884bV1lZmXTxxRd3rFy5clf/Y08++aTtoYceylEUhc2bN6/78ccf3zP8F3dklLjJsBQtX5ME4GTO+WxwPheMHc8Y21tMhxL0kCQBODm89fNWO5wboSbx9QA+dtZUb9ciuHimKArOP//88VdffXXrzTffvD0UCuGKK64ovPnmm/OeeOKJ3Yc654knnsi65pprOg+VuM1mM7///vsbv//+e9OmTZtM/fubm5vFe+65J3/9+vXVubm5oQsvvLDojTfeSD7vvPOiMrhNiZsMStHyNRYAp3KuzIaizIMgTmKM6RhjACXpaDABqAhvAIBqh3MngPfC24c0EHp0b775ZrLBYFBuvvnmDgDQ6XRYsWJFQ3Fx8aSHHnqo8dZbb8396KOPUhhjfMmSJe2cc7S2tkqzZs0qtdlsoa+//nrLwOtZrVblrLPO8tTW1hoG7q+trTUUFRX5c3NzQwBw+umnu1955RUbJW5yTIUHEOdwrsyBopwBQTyOMSYyJgAi3belkbEArg1vSrXD+S32JfLPnTXV0e6/jzuVlZWmyZMn9w3cZ7fblZycnMAjjzySvmvXLn1VVdVmSZLQ0tIiZmVlyY8//njWxx9/vKV/VZvBKC8v9+/YscNYW1urLy4uDqxevdoWDAaj1qKhxE32Klq+JoXLoYVcCV3OdPrZjAkmStQxSwBwQnj7JYC+aofzEwDvA3jXWVNdqWVw8eCTTz5JXrZsWZskqbNJs7Kyhj2mkJGRIT/88MP1ixYtKhYEAdOnT/fs3LnTcPQzh4cS9yhXtHxNNg8FLuKKfDmTDCcyUScykX4t4pAZwNnhDdUO5zYALwF40VlTXaVlYFqaOHGi9/XXX99vgLyzs1NoamrS5+fnH7Xc6sqVK1MffPDBXAD429/+Vjdz5sy+wx17xRVXuK644goXAPzhD39IF0VxpOEfFjWlRqGi5WuKx/y/f9055v/96zvOeSPT6R8T9KZTGBOi95tGjrXxAO4CsLna4dxY7XD+strhHOoc9ri3cOHCHp/PJzz22GNpABAKhbBs2bKCRYsWtc+bN8/9xBNPpAeDQQBq/W4ASEpKkl0ulwAAixcv7q6pqamqqampOlLSBoA9e/boAHXB4Keeeipz2bJlbdF6XdS0GiWKlq+xKb7e6yDqlgqSoUSQjFqHRI6d48Lbg9UO51dQW+KrnDXVzUc+LcIGOX0vkgRBwOuvv77t2muvLQxP1cPcuXNdjz766B6dTse3bNlicDgcE3Q6HV+yZEnbnXfe2bZkyZL2s88+uzQrKytw4OAkAOTl5R3n8XjEYDDI/v3vf6euXbt2y7Rp03zXX399QVVVlRkA7rjjjsZJkyZFfAGFfqOiyBRj7HwArwFwcs5rInTNGwH8HMA4ABmc8/bwfhuAp8P7fQCWcs43ReI5hyP/p8+eywTdLYIpeTYTqA/k0vWrdl3V8E1UVt6OMwqA/4OaxF911lR3RfoJqMjUkVGRqaO7HMBn4a/3RuianwN4C+ov/0B3AviOc34BY8wB4K8ATo/Qcw5KwU0vZvNQ4FbBmHSlLjk981g+N4kbAoC54e2RaofzeQCPOmuqNWtkkMFL+MTNGLMAOBXAHABvAriXMTYbwK2c8/nhYx4DsI5z/g/G2LkA/gSgF2pyLu4/biDO+YbwuQc+VA7g9+FjahhjRYyxLM55SzReX7+i5WuEUE/7hUzQ3SyYrCczQaDxCzJYJgDXALim2uH8AMCjAN6i2/BjV8InbgDnAXiHc76FMdbBGDvsbbeMMSOAJwDM5JzvZIy9OIzn+x7AhQA+ZYzNgFo8KR9AVBJ37tLHrILBfLdgSlmqS04/7ArWhAzS6eFte7XD+RiAp5011W6NYyIHGA2J+3IAj4S/fyn881uHOdYBYAfnfGf45xeh3uwwFL8H8Ahj7DsAlQA2QK31HFEZC28vlDLHPqCz510s6PQ00kgibRyAhwHcX+1w/gPAX5w11YNaMDcS1q1bN81oNHo554wxxm02W0dubm5LJEopbN++vcjtdqfodLrQcccdt7l/v8fjMdXX1xcqiiLo9frAuHHjduh0upj81JHQiZsxZofah3ccY4xDLajPAbyB/adCHjXxMcb+DSALapfK1Yc7jnPuBvDj8DkM+8p7RkTGBXc6pbT835tLTz6H6aRB16EmZJiSAfwMwI3VDudaAA85a6o/jvaTCoKgTJw4sQoAAoGAbvv27cWyLItjxoxpHOm109PT2zMzM1vr6ur2mx5ZX19flJeX15CamuppaWlJa2xszI7E80VDQiduABcDeI5zfl3/DsbYx1CTdjljzAC1f+90qIOXtQCKGWNFnPM6AJf2n8c5P2swT8gYSwXQxzkPALgawCfhZD4imRffM02y5f3ePL5iDhN1NN+aHGsMwA8A/KDa4XwXwHJnTfWGY/HEer0+VFRUVFdTU1NeUFDQCAD19fX5vb29yYqisIyMjNbs7Ox2ANi9e3d2V1eXnTGG5ORkV2Fh4UEV+lJSUjw+n09/4H6/329ISUnxhI9xb926tRQAJW4NXA7gvw7Y9y8AlwF4GcAmqC3iDQDAOfcyxpYBeIcx1gvgm8NdmDF2E4DboZbo3MgYWxtuiTsBPBtu4W8G8JORvIDMRfedJtlyf28qnn4iDTiSGHEmgHnVDufLAO5y1lRvG8xJxz173NDKulbi4OM37ttXuaRyvaIorKqqypGSkuL2er1Gt9udWl5eXiOKohIMBkXG2LSrr7665cknn9wNAPfcc0+Wx+MRH3zwwYOmKRqNRl9nZ2dqWlpad2dnpz0YDB6U3AdatGhR0QcffJCSlpYW2rp1694uly+//NJ0ww03FPb19Qn5+fmBV199dYfdbo9ol0tCJwLO+RzO+TsH7HuUc34D5/x2znkJ5/xMzvmFnPN/hA/5iHPugFoDQgGw7jDXfpRzns8513HOc/u7TzjnX3LOSznnZeHrDnl+rLmkgqWd9dMZedes+MJUPO1jyZ5Hs0RIrGFQP5FWVzucK6odzpxjHcCmTZvKq6qqnLIs63w+n9Htdlvtdnu7KIoKAEiSJOv1er527VpbU1PTURupRUVFdW1tbRmbNm1yyrIshBtfh7V06dL21atXH9Tvf8011xT99re/3b1ly5aqhQsXdv3617/OHv6rPDRKBge7JjywuBnquoZPHMsnt04/v8xacdFay6R5n0lpBScxJlDNVBLLdACuA7Ct2uH8XbXDmXqsnnjixIlVEydOrJo8eXKlzWY7ZHekKIr8oosuYnffffeE5ubm/ZbE2717NzvxxBNLS0tLy0866aTSPXv2KBkZGTvOPPNMXWpqaqder/e73W4hOzt7kt/vP+jv8JxzzvFkZGQcVEGwvr7ecM4553gAYP78+e633nrLduAxI0WJ+wCc84c551M45+Wc8x9yzo9YnyBSzCUV6ZkX3/u31FN/uN6YP+FsJtLAI4krZgDLAeyodjjvqHY4TUc7YaQURWEA0NfXZ5BlWbBare7Ozs50WZYFAAgGgyIA3HfffZvefvttRZKk/eqX/+53v5N++MMfdmzZsqXq0ksv7bjhhhsK09LSZKfT2fevf/1rTHp6euuqVatSZs2a5TIYDIO+xXz8+PG+f/7zn6kA8Pzzz9ubm5uP2OUyHJS4NWYuqTDbz1x2k33eDZXm8TOuEQzmJK1jImQEbFCnxNbC7zdH84k2b97srKysnFBfX1/IOWd2u91ttVq7q6qqnJs2bSpvbGzMBtT624sWLer4/e9/nwkAXq83taamxrFx40bhxBNPzGtpaUm/4YYbOtetW2fZuHHjxDPOOCNp7dq1uszMzI6XX37Zftlllw2pu/Ppp5+uW7FiRcaECROcPT09giRJEa8rkuiDkzHLXFIhSPb8WSknXfonfXbJZCZQlwhJKAWspwf+ujpJys3dJej1wUg/wXHHHXdQudr8/Pzm/Pz8gcWzMgHgl7/8Zcvxxx9fftlll7WbTKbuKVOmNDLGJk+aNKnSYDBwv9/PGGPKpEmTNhUWFgrl5eUTWltbxU2bNpkXLFjg3rZtmzR//vwSAFi6dGnb7bffftjKf1OnTvV9/vnnWwFg48aNhnfffTfi3UfU4taAuaQiP8k5a2XKKVesNeSWTaGkTRKV4vGkBrZtmxBqa8s4+tHRk5WVJS9YsKDrhRdeSO/fN3Xq1N6nnnrKBgBPPPGE/YQTTuifCqhMmjSp97rrrhtz+umnu3Q6HcaPHx/sL+96pKQN7CvvKssy7r333pyf/OQnrZF+PdTiPobMJRVm0WK/JPW0H92lzywep3U8hBwLXFHEYEvLmHWnvNYr5efvFIzGqJU7PZJf/epXzc8+++zeN5AVK1bsWrx4cdEjjzySnZaWFlq5cmVd/2OXXHJJ19KlS4vfeuut2sNdb8GCBWO/+uqr5K6uLl1WVtak5cuXN95yyy3tTz/9tP3vf/97JgCce+65XTfddFNHpF/LqCjrGgvMJRXjjWMmPWCZNO8HgiHJonU8oxWVdT12gn99DCVZWfvvZEzRpWc06jIzInL7ejyjsq4xzFxSoWeS8XzrjAvvMeRPKGej/beVjG6cC6G21nylpydVys+r06r1He8ocUeRuaQiT59dclfylHMuFZNSIz6Xk5B4pfi8Fv/27eVSdna9Li2t8+hnkIEocUeBuaRCgCDOsUw++0FT8bRpTIjiqqGExCvOhWBT01jF60uS8nIb6MPo4FHijjBzSUWaYE5dlnLiouslW06u1vEQEuvk7q5M7veZ9WPGbGeSdNCdiORgNB0wgswlFcfr7HmP2mb/+BeUtAkZPMWrdp3IHg/dgDYI1OKOAHNJhQDgB4aCiTdaj58/k9HCBoQMGQ+FpEB9fZmUlbVLl55OiwwfASXuETKXVBgALEkqn/Vjc9lpM6iKHyGHVnfxosEcxqAu91d4tAOdNdXrj3aMKIrTSkpKvKFQiImiyC+77LKOe+65p0WMwLATlXWNU+aSChuYeLu14uKbkpyzqF42ITHGYDAoNTU1Vdu2bdv84YcfbnnvvfdSbr311oh0Y1JZ1zhkLqkYIxiSfmOb8+OrjfnlE7SOhxByZHl5eaGnnnqq7plnnslUFAWhUAjXXXdd/sSJE52lpaXlDz300N7b4X/1q19ll5aWlpeVlZUvW7Ys71DX07KsK3WVDIO5pGKyaLHflnra4rNFszVN63gIIYNTXl4ekGUZe/bs0a1atSo1JSVF3rRpU7XX62XTp093LFiwwL1x40bj2rVrU9evX1+TnJystLS0DKlfpb+s65VXXtlNZV1jgLmkgplLKs4Skmx3ps5aQkmbkDj2/vvvW19++eU0h8NRPnXqVGdXV5euqqrK+N5771l/9KMftScnJyuAWqBqKNelsq4xxFxSwQCcL5hTLrPNumqOaEympE1InKmqqtKLooi8vLwQ55z98Y9/3HXRRRftt3rO22+/bT3wPCrrGofCSXuBYLJeapu9dLZoSta0RCUhZOgaGxt111xzTeGPf/zjVkEQMG/ePNfjjz+e0b8s2caNGw1ut1s466yz3M8//3x6T0+PAAAtLS0ilXWNM+Gk/QPBaLkinLQztY6JkHhU9OorEbkOE8SQvnDMlsEc6/f7BYfDUd4/HfDSSy/tuPfee1sA4JZbbmmvq6szHHfccc7wCjrBtWvXbr/44ovd3377rXnKlClOSZL4GWec4Xrsscf2HHhtKusao8JJ+xzBYLnSNvcns0RzyjFfyZpEFpV1PXYOWdY1QpggyFJh4RYxKemYrAkbDSMp60pdJYcRTtpnMkPSlbY5SylpExJDuKKIwV27ShS/P+IzNuIBdZUc3lww4Ue2WUtOFJNS4zZph9xtaF/zJyi93QAYLFPOgvWE89D9yXPo2/Y1wBhEcyrSzv05dAeMtwZadqDj3b+C+72AICDlpEuQ5JwJAGh78yEE2+phGjcdtllLAADdX7wEfXohzKUnHeuXSUYhLsu6QH39eMO4cTVMFCN6Z2Kso8R9COaSipkAlqScdEmpLjm9SOt4RkQQYZvzExiyx0Px96Hp2Z/DWDQV1oqLkDrzSgCAe91quL54EWln3bjfqUwyIP0Hv4Bkz0OopwPNz/4cprHHI+Rug6AzIHfpY2h56S4o/l4oQT8CjbVIPfkyLV4liUWKAs45olmulQcCpsCuhrH6osLt8VQWVlEUBmDYbzbUVXIAc0lFKYClSeWzUgw5pTO0jmekdBY7DNnjAQCCwQwprQByTwcEg3nvMTzog1oiYn+SPQ+SXb1pTJecBsGcArnPBSbooIT84FwBV0IAE+D69HmknPrDY/KaSHxgDQ3oDgYR7XE0pdeTGmpujptqnIqisLa2thQAm4Z7DWpxD2AuqUgD8DN9doloLjv1XK3jibSQqwWBlh0w5JYBALo+WYneTR9CMJiRdfnvjniuv7EWXA5BZ8sBYwJEUwqa/nEzLBPmINTVBM753jcIMnJNwSB+2dSEdjkEBuCS1FRcabPjsfY2vOpywRYukvTz9AzMshy8hOmnvR78rqUVMjguTknFNWlqN9htjY3Y6vdjlsWCWzLUWa0rOtoxXm/AGcnJEX0N4oon0HH9dWgvKACiXcanpSWHNzRIMBh6o/tEEaEA2BQKha4e7gUocYeFq/z9VDBZLdbp589ngphQ/zZKwIu21x6E/fRr9ra2bTMXwzZzMVxfvoye9W8h9bRDt5hDnk60r/kT0s+9BYypf4D2M67d+3jrq7+G/awb4fpiFQKtO2EsmoLkKWdH/0UlMB1juD0zE+VGI3oVGRfX1eEks1qqerHNhqX2w9//JXOOB1pa8FR+AbIkCZfW12GOxQKZcxgFhtfHjsVPGnahR5bh4xwbvV5cn5Z+2OsNF3O7ofvvhyJ+3SMwAzjLWVP97bF8Ui1QVwn2ziC5HEwoTj3tRzMFvSnidzppicshtL32IJLKZ8NcdvJBjydNmI2+LZ8f8lzF34e2V3+N1NOuhCHPcdDjfVu/gj57PHjQh2B3EzLOX46+2s+hBH0Rfx2jSYZOh3KjWtY9SRBRbDCgNTS4xWEqfT6MkfQo0OuhZwznJFvxoccDHWPwKRwK5whxDoEx/KW9DTemJ8z9ZGYAr1c7nNGZgxhDKHGrZgGYGx6MLNY6mEjinKPj7UcgpRXAOuOCvfuDnfvuJ+jb+jUke/7B58pBtL32AJImzEWS49RDPB6Ce90bsFZcBB7yY28/OVcAmVagipQ9wQCqfT5MCifyF7q6cP7OnfhVUxNc8sFlNFpCQWRL+z4wZut0aA0FMc5ggF0n4qL6Osy2WLArEIAC7H2DSBAFAF6pdjgTOrclVHfAcJhLKsYDWGIqniYZchJvHpt/TxV6N38EKaMIjc/8DIDaReLZ+B6CnbsBJkBnzYD9rJ+qxzdthee7t5F2zk3orfkMvobNkL098Gx6HwCQfu4t0Gep7209366BZeLpECQjpIyx4CE/Gv/+U5jGnQDBeHC/Kxm6XkXBzXv24JeZWbCIIi5LteGGtHQwAI+2t+O/W1vx25zBz1b9Zea+xuiy3Q24LzsbKzraUev342RzEhalJsSHzdMALAPwmNaBRMuoTtzmkopUADcxydCTVD7nPK3jiQZj/gQU3vHWQftN46Yf8nhDTgkMOSUAAMuEObBMmHPYa1un7/snY4whY+HtI4yWDBTkHD/fswfzrSmYFx44TNft+5NdlJqCG3bvPui8LJ2E5uC+TzzNoRAyddJ+x3zQ04NyoxF9CkdDIIiHc/NwTUMD5lutMCXGeiAPVjucrztrqg/+B0oACfE/NBzhfu1LAJisJ5w/VTCYI17snJDh4pzj7uYmFBv0uMpu37u/bUA/9/s9HpQYDAedO9FoRH0wgN2BAAKc4+0eN+YMmHkS5BzPdXXhJ/Y0+BQF/dOfZXAEE6cERjKA/9E6iGgZzS3ucgCn6LNL+vQ5JQeP2BGioW+9Xqx2u1GqN+CCup0A1Kl/a3vcqPH5wQDkSRLuy1ZXxWoNBXF3czOeyC+AjjH8KjML1+xugALggpSU/RL8i11dOC9FbVmXGQzwKQrO27kTMy1JsEZgLcYYsqDa4bzEWVP9staBRNqoLDJlLqkwAngAjIlpZ/3sYjEp9eCROZKQqMjUqNMCwOmsqe7SOpBIGq1dJT8AYLdMPruUkjYhCS0LwB+0DiLSRl3iNpdUFACYL1oz3aaiqadrHQ8hJOqWVjuchx9lj0OjKnGbSypEAFcB6LMeP38uE3UHj+wQQhLRY9UOZ/xUoTqKUZW4oc7vHC+ljQnp7HkTtQ6GEHLMlANImCm/oyZxhwckLwHQlFQ+6xQWTzUgCSGRsFzrACJl1CRuACcBMInWTJ2UXjhF62AIIcdcRbXDOVvrICJhVCRuc0mFBPVjUptl4tyTmCAk1GRVQsigJUSre1QkbgBTAaQKJquizyw+QetgCCGaOava4Yz7T9wJn7jDM0kuBNBpmXj6dCbqRuXiooSQve7QOoCRSvjEDWAigGwmGfv0uWUVWgdDCNHcomqHM67LNyd04g4XkjofgMtcUlEm6PRJWsdECNGcCOBnWgcxEgmduAGMB1AEoEufXeLUOBZCSOy44OiHxK5ET9wnAAgynV7UWTNLtA6GEBIzCqsdzslaBzFcCZu4w4OSJwPoMBWfMI4GJQkhB4jbOykTNnFD7SIxA/Drc0rLNY6FEBJ7EjtxM8bGDmZfjJkCQIEgClJqTqnWwRBCYs7x1Q5nXJZ1HmyL+1+H2PdqJAOJJHNJhQDgVACdpqKpRUwnmbSOiRASkxZqHcBwHHHpMsaYA8AEACmMsQsHPGQFYIxmYCOUDyAFwC5DrsOhdTCEkJh1HuJwbcqjrTlZBmA+gFQACwbs7wFwTbSCioBJADgAiNZ0WqaKEHKVy+/wAAAgAElEQVQ4c6odzmRnTXWP1oEMxRETN+f8DQBvMMZO4px/eYxiioSTAHQxyaATjJYMrYMhhMQsCWqvwldaBzIUg13lfRtj7E6oMzX2nsM5XxqNoEYiXHc7B0CDPmt8LmNCIs+cIYSMXBkSNHG/AeBTAO8DkKMXTkTkQO0m4ZNtvvQM/9ctHcxu6BJs+m7RZuJMpAUUCCEDlWkdwFANNnGbOedxUVGLgecCEDkY7s34KG9Oypqs/sc4B7yy4PPIOn+Pove7ZGOoSzGHOrmFd/Bk3oEU1o5UoYPZdB3MLnUyu6FTtBn9glnS8CURQqIr7iYwDDZxv8UYO5dzvjaq0UTARLZjtg7yyQqEinRJ3m+OJmOAWacYzbqAMRMBAJ5BXTOoINQr63w9suR3y4Zgt2IKdSlJcgdP5h3cinakCB3MJnYwm9Qh2KUuZjN2i6lGUOuekHiQsC3umwHcyRjzAwgCYAA459watciGKYu5dJzjTQUIFCWbblRDHRlJgC5VCFlSpZAF8ALoPuo5Cgf3yoKvV9b53LI+4FKMwS7FLHcqFqUDVnTwFNbOUsVwwtd3CnZ9l2A3+QXTYP9PCCGRMb7a4RSdNdWx3g2816CSBOc8OdqBRMLCMokByGUMrSIgmyVoduONwMCSdIoxaYit+0C4de+RJb/rwNY9rKwdqawDNl0Hs+s6BJu+i9kN3WKKiTEagyVkmPRQJ15s1ziOQRtU4maMzTzUfs75J5ENZ8SSoE7vkW1G6EWBxd3aknoBOr0QstikkKVgiK17j6zzq617k9q65xbeya28vb91D5uuQ7BLnYLN0CXYjAFq3RPSrwyJlrgB3DbgeyOAGQDWA5gb8YhGJgmAAgDZFmHULJoQbt2bknQBU9aQWvcs2BsSfT2yFHArxkCXYpIHtO7RgVSxHTahg9n0/X33LjHFSK17koAKtQ5gKAbbVTLwrkkwxgoA/DkqEY2Muf+bjCRmPtKBBNALXNLrQ5INIah9911HPUfh4H2y4PPIkq9H1ge7+/vuebLSwZPRgVTWjv7W/b6++6BgiLtPP2RUMWgdwFAM96PybgCxuKKMGeHRSJuREnc0CAzMolNMFp3flA0/1OoHR+eXWbBXFn09st7vVtS++07FooRn5rAOpLJ2ZtN1CDZdeN69wS1YqXVPjpXES9yMsb8gXPsDakXBKQC+jVZQI7A3cYtCBKaTkIgxiFwyiCHJjlAy0IchtO69Hlnyu2V9oFsxhboVs9yhWHgHrLwDKUI7bEI7s+k6mU3qFOzGLsFuDAl6at2ToYqrhVYG2+JeN+D7EIAXOeefRyGekTIhXKo2pKh93SR+hVv3ZovObx5i6z4Qbt0HXIoh2OI1mHtlQ6hXtPa0pgR8X3lD9dGNnMQDmcNg1YsNOpF5O5OZJxa7EA5nsH3czzLG9AD6FySojV5II8YBQKbEPWoZRK43iCG9HSEAfeqQtSqt24bgzDPzFW4Q4uqjMYma7PDXD6/WNIyhGewKOLMBbAXwV6i1a7ccboqgxmT0J25OiZscLFWENKO+J6h1HCTmxNXvxGC7Sv4I4EzOeS0AMMZKAbwIYFq0Ahumvck6pHBK3OSQfiO6xXmyNchERjVoSL+4StyDHbKX+pM2AHDOt0C90SXW7E3WngACWgZCYleOwE0T2nobtY6DxJSj3+kWQwabuNcxxp5ijM0Ob09i/wHLWLE3cdd3K24tAyGx7Td93XZOn8rIPnVaBzAUg03cNwCoAnBTeKsK74s1XoT7uJs8vC+k8JDG8ZAYVcqU5DEd3t1ax0FiRp3WAQzFYGeV+AH8KbzFMjf2zTdHXxBuqwF2DeMhMey+nu7kpekmMEZT/kc5DmCX1kEMxWBnlcxnjG1gjHUyxtyMsR7GWCx2RbgxoI6rJ8BjMUYSI2awkC2jy0+tbtJcuaTSr3UQQzHYrpI/A1gCII1zbuWcJ8diLW6od2iw8IZuH4+rAQdy7N3R3R1Xd8yRqKjTOoChGmzibgCwiXPOj3qkhlbXBmUALoRvX93l4s3aRkRi3dkskGlxBej3ZHSr0zqAoRps4r4dwFrG2C8ZY7/o36IZ2Ag0Q731HVVt8h6NYyFx4Ked3THdICFR953WAQzVYBP3b6FWBjICSB6wxaJqABYAWNcoNyucpnyRI/sRfDl6T7Bd6ziIZj7WOoChGuydk7mc84lRjSRy6hDu4/YEEOry8tY0M8s+8ilktFvc4fI9ZUnXOgxy7HmgLgoTVwbb4l7LGDszqpFETiMGzCxp7OHUXUKO6qdyb57glWkwe/T5vHJJZdzd7zGUG3DeYYx5Y3w6IAB0QL0RRwKAjS3yDm3DIfFAxxi7oM0Vq7/TJHr+T+sAhmNQiTu8yns6gNkAFgCYH/4ac1bXBjmALQCsAPDOttA2WeGytlGReHBHsCcPAWVwC3aSRBF3/dvA4G/AuRrqC3wHwH3hr/dEL6wR+wbhKswuPwJ7enidtuGQeGASmDi3tYcGKUePXqi5Iu4MtqvkZgDTAdRzzucAmAp1vnSsqsWAfu5NrXIsL/xAYsi9flcuDylxdRcdGbbX47F/Gxh84vZxzn0AwBgzcM5rAJRFL6yRWV0b7IC6oHEyALy3PUSJmwyKXYD+hLbeJq3jIMfEP7QOYLgGm7h3M8ZSAbwO4D3G2BsAYn3dvi8A2ABgexd3N/UocVVEhmjnN97uTC5TZckEtwvAh1oHMVyDHZy8gHPezTm/D8DdAP4O4PxoBhYBmwf+8HmDHHdzNYk2Chg3l7X30TTSxLayckll3N6cN9gW916c848556s557G+wkwDgFaEu0te2Rzc7Atxr7YhkXhxf2+3jRZaSGj/0DqAkRhy4o4X4WmBbwNqPW5vCPKmViXuahIQbUxgsjWv00et7sT0WeWSyu1aBzESCZu4w9ZDXfldBwCv1wTXx3iBQxJD7nF3men3JSE9pXUAI5XQiXt1bdAD4DMAmQCwsUXp2OXi27SNisSLU1gozd4doFZ3YtkB4J9aBzFSCZ24wz7GgBXpX6kK/p92oZB4c1tX12ALsZH48Jt4nbs90Gj4payD+i6bCaDjk3p5z8XlytaiVKFE27BIPFjAAlkPuAMtfVZ9ltaxDBToCGDPk3sQcqs5yDbbhvQz0xHyhNDweAOC7UFI6RLGLBsDMUnc/9z2AHb9ZRegAFzmSDsjDfa5dihBBbse2YVgVxD2uXaknZ4GANjzzB7Y59hhKjId89cZYVsBPKd1EJGQ8C3u8CDlqxhQP/ylTcGPtIuIxJvrOl0xV+uGiQzZl2Wj5MESFN9djM4POuHb40P7mnZYnBaU/lcpLE4L2ta0HXSuLlWH4ruKMf4341F8TzHa1rQh2BWEZ5MH5lIzxv9mPLq/UAslend5wRWeCEkbAO6vXFIZc/+Xw5HwiTusBuq7bToAfNEgN+3oUmq0DYnEi6Xcm6vrDXVoHcdAUqq0N5mKJhGGXANCXSG4N7iRemoqACD11FS4vz244KGgEyBI6p8+D3F1jXOobwZKQAGX9+1r/d9WZF0YUx82hqsGwAtaBxEpoyJxH6rV/fzGwEcKTRkgg3RFu6tP6xgOJ9AWgK/eB9M4E0KuEKRUdUhHl6JDyHXo7txARwBb79qK2l/UIv3cdEg2CZYJFgTbg9jxmx1Im5cG9wY3jIVGSDbpkNeIM7+O5xtuDjQqEnfYFqjLmmUAwLpGpfX7ZiUuK4ORY+/nsief+eSYq9ct+2TsemwXsq/Ihmjavy+bMTag1Nr+9Gl6lDxQgtL/KkX3590IuUJgIkPB9QUYf/94pExPQce7HUg/Ox1NLzZh12O74N4Qcy9/sL4A8LLWQUTSqEnc4Vb3K1DXoxQA4NGvAx96g7xX08BIXJAYY/Pb3F1axzEQD3E0PNaA1JNSkXJCCgC1lR3sDgIAgt1B6KxHnn8g2SQY8g3o3bL/n0HHhx1IPTkV3u1eiCYRBcsK0P5O/FW85Zz7AVydSK1tYBQlbgBYXRvcDnXFixwA6PBy/5tbQv/WNCgSN+4MuvMQUGKiy4Rzjj1P74Ehx4D0s/etlWmdYkX3Z+rAYvdn3bBOtR50brAzCCWg5jG5V0bflj4Ysg17H5d7ZfR834PUU1LV48Ktdh6Iv55FxtgDlUsqq7WOI9JGw3TAA/0vgBkATAC8z28MVp6YL04akyKM1zguEuMsjOlOa+vZ82leSqHWsfRt7UP3F90w5Buw7W71nrKsi7OQPj8dDX9tQNenXZDSJBQsKwAAeHd60flRJ/KW5sHf6EfTS01gjIFzjvRz0mEsMO69dusbrciYnwEmMFgmWtDxQQdcd7lgn2PX5LUOF+d8I2Psv7SOIxrYaByfW1gmnQbgagA7AaA0TUh58HTDDXqRGY58Jhnt2jj8c8YUgOnodyWWcc5lxtiJlUsq12kdSzSMqq6SAT6HOlCZCQBbOhTX/1aH3tQ2JBIPMhgMk9s8tNBCjGOMPZyoSRsYpYl7dW1QAfAsAD0AIwC8UBncvKlV3qBpYFHkC3HMeNKDySs8mPA/Htz7kQ8A8Nh/Ahj/aA/Yr91o7zv8+I14vxtTVngwZYUHC1/c1837w//tw6THPbjzA9/efQ984sfrNcHovRiNPdDnyuC0AHXMCq/QFctr4o7YqEzcALC6NtgEtSZvLsLDL7/71P92t4/H39D5IBhE4MMlSfj+egu+uy4J72wP4avdIZxSIOL9xUkoTDnMvLEwkw747noLvrvegtWXmwEAG1tkmHQMG2+w4JtGGS4fR1OPgq/3yDjfkRBzfw9prKAkjWv3UvGpGMQV7mGMnVe5pDKha++P2sQd9jnUOZ75ANATQPCx/wReDSmJt2wVYwwWvZqcgwoQlNV3q6k5IopSh/drIAmAN8ShcI6gDIgCcM9Hfvx6duJ3/97v6Urho3GAKIZxzjkT2BWVSyq3aB1LtI3qxB2e2/08gC6E16f8zx65Zc2W0FpNA4sSWeGYssKDzId6MK9Yh4r8wU8q8oWAE/7mwYlP9e7tBnFmiMgwCzj+iV4sKNVhW6cChQPH54hHuVr8m8zklOxOP7W6Y4mCByqXVI6KsapROavkQAvLpGKoa2k2AQgAwN0zDfOm54knaxpYlHT7OC5Y1Ye/nGPExEw1yRb9uQfrrk1CuvnQ7+V73AryrAJ2dCmY+2wvPlichHH2/Y9d8GIfnphvxDMbgvi+Rca8Yh2umaaP+uvRyodcar+5OCf96EeSaOMh/g7TsXMrl1SOioQ2qlvc/VbXBndALUCTj/C/yW8/9b+fqIWoUo0Mc4p0eGfb4HuE8qzqr0qxTcDsIh02NO8/NvdGTRDTcgR4AhzbuxS8vMiMV6uD6Asm7t/RXBZMt3b7G7WOY7TjMt/BdOzS0ZK0AUrcA70P4D0AYwBA4eB3f+j737ZeJSGmfrX1Kuj2qb/X3iDHeztCcKQP7r+/y8vhD6nntvcp+LxBRnnGvnODMsefvw7g9lMM8Ab3lceQFSCQ4HMvbunqTvx+oRjGFe5hIptfuaQybgupDAcl7rBwf/dLACoxYLDy/o/9L/QGeNz/UjR5OOY824tJj3sw/clezCvWYX6phEe/9iP/Tz3Y7eaY9Hgvrl6tDsava5T3fl/dLuOEJ3sxeYUHc57tw/JT9CjP2Jev/vpNAEsmSzBLDJOyBPSFOI573INpOSJSjUeerRLvLoY/y9gTbNU6jtGIy9wH4IxEvKX9aKiP+wALy6QkAHdCHaxsBYCp2UL68lMNV5kklqRpcCQmrWCmPX8tysjTOo7RhMs8qASVs6uuq/pQ61i0QC3uA6yuDfYC+DPU1eFtALChWWl/6Av/Sl+Ix0SBIRJbrlX68sS+UExVDkxkXOGy3CdfPFqTNkCJ+5BW1wbbAPwBgAFAKqDW7/7jF4GV/hBP6In9ZOgExnBJu9ujdRyjAVe4IvfKP6y+sXq11rFoibpKjmBhmTQWwB0AvABcAHBygZhzy4n6xQYdMx7xZDKq+DhXZuTl93KDmHz0o8lwcM657JF/Un1j9TNax6I1anEfwera4E4ADwFIAmAF1PUqH/068By1vMlARsaEs9p6OrWOI1GFk/ZPKWmrqMU9CAvLpBIAtwNwA+gB1AHL204x/MiiZymaBkdihosjeGpBfgiSkBBLoscKLvNQsDt4Ve0vav+pdSyxglrcg7C6NrgVap93MgYMWN75ge/vHX1Ki6bBkZiRwiCd2Oah34cIUoKKz7fHdyEl7f1Ri3sIFpZJhQBuhfqG1wYAdhMz/Hau4dI8qzBW0+BITGhSmHdeUb6OiSxxyyMeI7JXdvmb/Odu//X2L7SOJdZQi3sIVtcG6wH8BoAH4XUrO73cf8u/ff/c0iFv0jQ4EhNyBG6a0NZLt8GPUMgdavDu8E4datJmjHHG2B8H/HwrY+y+SMTEGHuaMdbKGNt0wP7JjLEvGWOVjLE3GWMHL/QZYZS4h2h1bbAVwIMAGhC+Pd4Xgnzbu/5//V9d6AOFPsKMer/p67bTQgvDF2gLrO+p7Jm087937hzG6X4AFzLGolH86x8Azj7E/qcALOecHwfgNQC3ReG590OJexhW1wZdUPu8vwMwFoDIAfzpy8BnT64PPu+jGSejWilTkgs7aKGFoeIK596d3hc6P+6s2P233d3DvEwIwN8A3HLgA4yxIsbYh4yxjYyxDxhjYxhjKYyxesaYED4miTHWwNjBXV2c808AHGrmUCmAT8LfvwfgomHGPmiUuIdpdW3QB+B/AKwGUAjADABrtoZ23PWh/4lEKU5Fhufenu5kWmhh8GSv3Ote7/5Z6xutP2p7s22kn1b+CuCHjB004+svAJ7lnE8C8E8Aj3LOXVAbYLPCx8wH8G/O+VDW3tsM4Lzw94sAFAw78kGiwckIWFgmHQ/gegBBhActzRJ0d880/GBCpjhF0+CIZuakZO5ptxuphslR+Jv9O1z/cV3R8q+Wr0d6LcaYh3NuYYzdD/Xv0QvAwjm/jzHWDiCHcx4Mt6ibOOfpjLErAMzknF/PGHsNwP9wzt87zPWLALzFOZ84YJ8DwKMA0qA25G7inKeN9LUcCbW4I2B1bfBbAPdBneddAID1BRH65Qf+N16tCr4WkLlf0wCJJu7o7qaZJUfAFa54qjyrm19qPiESSfsAfwbwE6g3zx3NagBnM8bsAKYB+JAxVsAY+y68XX+kkznnNZzzMznn0wC8CGD7SIM/GkrcEbK6NtgIdcbJeqj93gYAWPl9cOPy9/3/s8etDGeghcSxs1kg0+IKNGsdRyyS+2RP9xfdt3a823Ghe4M74gW6OOedAF6Gmrz7fQHgsvD3PwTwafhYD4BvADwCtTUtc84bOOdTwtuKIz0XYywz/FUAcBeAIx4fCZS4I2h1bbAP6n/aPwBkhDds61Tcy9b4Vr67PfTvRFyImBzezzq7qS/yAL4GX3X7u+2zdz+5+2H3Bnc0Z9/8EcDA2SU/A/BjxthGAFcCuHnAY6sA/Cj89ZAYYy8C+BJAGWNsN2Os/03hcsbYFgA1ABoBRP22fOrjjpKFZVIugGugtr53Qx3txvE5QsaNM/QXppuFbC3jI8fOtIyc9oBFGvVrU8q9co9rnev5ng09v3RvcLu0jieeUeKOooVlkgTgXAAXQK0u2AUAkgBh2XT9iTMLxdkS3WGX8B4VzLufLEzP1zoOrXCFK31b+iq7Pu+6W+6R10a5lT0qUOI+BhaWSeMAXAf1Y1sjwq3v8XbBeuMM/TnFNsGhZXwkukKc82k5+S7FJKZqHcuxFuwKtnd/3r2yb1vff7s3uKmOS4RQ4j5GFpZJJqit7x8A8CG8LBoAnO/QlSwql85NNrBR94c9WtwnWnb9a4x9jNZxHCs8xIM9lT1fd3/WfTuX+dfuDW5F65gSCSXuY2xhmZQHdWDECaAFQB8AWPTQ/WyGfub0PPEkncB0WsZIIs+rcHlGQYEXesGidSzRxDnnvl2+Ha6vXU/4G/1/o77s6KDErYGFZZIAYAbUBG4EsAeAAgAldiHl2mnS6aVpwnGMJfYK6aPNz6WU+g/yUwq1jiNa/M3+hu4vuj/07fI9BKDKvcFNySVKKHFraGGZlAzgfABzod7htbf75JQCMeeK46TTC1KEcVrFRyKrU0FgZmG+wnRCQi17F+wKtnV/1f11X23fkwDec29wU62eKKPEHQMWlkkFAC4BMAlAN8KzTwBgXrFYeHG5NDcnWRg1/aOJ7CqDrX59bnJCtLrlXtnl+sa1vue7nmcArInGjTTk0Chxx4iFZRKD2u99GdSiVR1Qb6EHACwo1Y0736Gbm5Ek5GoUIomABs76zinM1zMxfscxZK/s8VR6Nrn+43qBh/ir7g1uKqh2jI3axM0Yy4daRawc6h2kbwG4jXMeOMzxPwfwN8553yEeSwPwKoDpAP7BOb9xwGOXAvgVABHq7bR3HCmucP/3ZACXAsgG0A514QYAwKJynePcEt3sNLOQNYSXS2LIRea0+i1ZSXHX6g65Q209G3uqezb0vMtl/pJ7gzvqNTnIoY3KxM3UUb+vATzOOX+GMSZCreHbyTk/ZBF0xlgdgBM45+2HeCwJwFQAEwFM7E/c4YS+AcA0znkbY+xZACs55x8cLcaFZZIOasGbSwDYoSbw3v7H5xWLhWeP100vtglOUWBUuiCOVCmC+5KxeRYWJ/9vgbZAg/tbd3Vvde86AC8B2EQDj9oarYn7dAD3cs5nDthnBbAT6qo2v4a60oUC4EkADOrCCbUA2jnncw5z3augJvf+xD0dwO8556eHf74SwEmc82WDjTV892UF1Dq/VqirzO8t5j4mhVkumSAdPy1HPCFJz5IHe12irbOTMxr2pJuiXrd5uLjCFX+jf4frP64q3y7flwDeBFBN87Fjw2hN3DcBGMs5v+WA/RugFog6DcBlnPMQY8zOOe88Uot7wPlXYf/EbQNQCeBUqPVKVgHQc84XDDXmhWWSHmqL/lwA46DWGm5F+C5MSYBwoVNXNrtIN50WLo59n3Ndx3Vjc9JibcqnElS83jrvdtfXrupge/BDAO8AqKcWdmyJ2wGSKJoNtZB6CNhbHnJYOOddjLEboCZsBWpZyWFN71tdGwwA+HZhmbQBQD7UFTtmQe077wwq8KzaHKpetTlUPTVbSD/fIU2fkClM1ovMMNz4SfScwkJp9u7Ani6bQfOFFjjnPNgZrPNs9tR5Nnp28hB/D8CH7g3u1qOeTDQxWhN3FYCLB+4Id5WMAVB3tJMZYxcAuDf849Wc83WHO5Zz/ibUj5lgjF0LYEQFdlbXBjnUhYqfX1gmvQbgBKi30RdCvZW+bUOz0r6h2f92igEfXDpRmnRygW663aTWDCax47auLt2dNu2KRMo+udu73bvF/a27PtgRbAWwFsBn7g1u99HOJdoarV0lDGrh9Ec55yvDg5MroE6/2wrgDBzcVVIJYCHn/LALIhzYVRLel8k5bw13m3wE4BLO+ZZIvp7wTJTScNzHA+BQl1Dz9R9zRrE4Zl6xblqxTSg16FhC3QASzyrSslv6rPpjNkOIyzzkb/Fv82z01PXW9DYB+B7AhwBq3BvcQ1lnkWhoVCZuAGCMFUBd7NcBdTrgWgC3Qm0R/zfUwckggCc5548xxn4G4EYAjYcanAz3gVsB6KHeRHMm57wqXHx9cviw+znnL0XzdS0sk9IAnAzgLKjLNvVAvaGHA2pf+BnFusIT80VHSZrgsOiZNZrxkCP7BzM2/rEoM6pz87nMQ8GO4Pa+bX2Nnk2eJrlPboTad/0t3TQTn0Zt4k50AwYzzwJQAnVmTC/UGSl7ZwacXCDmnDZGdDgzRAd1p2hjamZuRyhJF9HFZZWQ4gu0Brb2bevb7dnkaecB7gfwOYDPAOyg2SHxjRL3KLCwTEoFUAbgJKjJXAAQgHp35t6Px450IXVesc4xIVNwZFvYGCHWpjwkqD8ISQ3PFqaNeGqgElA8/mb/lr7avkZPjacdMmQAG6Eut7XZvcF90M1jJD5R4h5lwnXBS6He3DMDateODDWJ7+0Tz7Yw0znjdWVTskVHQQobR6VmoyfIOZ+Wm9/DjeKQuq24zEMhV2iXv9m/q297X7d3h7cTHCEA6wD8B8AWKviUmChxj2Lhm3vGApgCtV/cCrUvvAsDbrO36KGbO1ZX5EwX8sekCPmZSSyPBjgj605dcv2bBbYj3gbPFS6H3KHdgdZAnXeHt6VvW18vD3FArSz5JYBvAWxzb3AfsmwDSRyUuAmAvTNT8gFMgHrDUE74IS/U2Tb+/mMZgMnZQvrUbDF/nF3Iy0tm+TYTy6KuleHzcB46Kb8gAL1g7t/HZR4MeULNwbZgvbfO29S7pbeXB/b+wdZBTdQ1AHbSOo6jCyVucpBwpcJ0qF0qE6HOvElBeGYK1JkqPRgwJ91qgFSRJ+ZOyBTzi1KF/GwLyzdLLKFXe4mkoMJDN8Ba9bHeFAy0BfZ4d3m7fLt8ISjor2eyB2qirgVQR/3VoxslbnJU4USeArVFPhZqq3wc1EFOBnWg043wMmz9xtsF6wm5Qn5pmpiflcSybCaWZpZgHe0t874g97T38eZdbqXzzVaWV+VlH33eydwhDhPUN0cB6rJ23wKohpqoe7SMmcQWStxkWMLVC7OgJvMyqOVxM6Emnv6phz1Qk/peJh1EZ4ZgH5sq2POtQlpmEktLMzO71cBsiZLU/SHu6w3C3ePnLpefu7u83NXex91NHsVV2aJ4mjxchLpkndwoimPqJF1jQBC+gZqkdwPY497g7j3ik5BRjRI3iZiFZVIS1EReALVVPh6ABWqXCoPakvRB7TfvQ7hAVj+dADY2VbCOSWEpOclCSoaZpdhNLCXFyFIselgkgRkkEXpJgF4nQK9Fjg/KPDAwKXf71KTc2svde9yKa0eX0uPyg0FNzIbwNvCPrAnA9vDWDKBlp6RzV27y0h8iGTRK3CSqFpZJZgBpUGuKZ2BfYquT5SoAAAHOSURBVM+Bmtz6bwQRoCY4H9SBUB8OaK0PxABYDdCnGpk+2cD0VgMzJEnQJ+mZ3iwxvVmCwahjeqMOeqMOer3I9AAQlBEKKTwUVBAKyggFZPX7gIyQP8TVr+Hv/TJCvhAPeYPq124fAh1ezrF/UmYHvAYF6qycNqjVG5vD3zcDaF1dG6TbysmIUeImmgj3mydB7Tu3hr+mQ131Jwtqku+f18yxf6sVUBNm/waorXoZaiteHrD19xkPZjvUc/V/3/883VAXtehPyp0AXFD7+F0AelfXBumuRBJVlLhJzAr3o+uhtmz1A7YDfzZCfRMwD9hM4U0HteXev/kP8b0/vAWhJv4DNw/UpOxZXRukaXdEc5S4CSEkzsTFmneEEEL2ocRNCCFxhhI3IYTEGUrchBASZyhxE0JInKHETQghcYYSNyGExBlK3IQQEmcocRNCSJyhxE0IIXGGEjchhMQZStyEEBJnKHETQkicocRNCCFxhhI3IYTEGUrchBASZyhxE0JInKHETQghcYYSNyGExBlK3IQQEmcocRNCSJyhxE0IIXGGEjchhMQZStyEEBJnKHETQkicocRNCCFxhhI3IYTEGUrchBASZyhxE0JInPn/KBrj+WhADnAAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def query1(list):\n",
    "    to = datetime.date.today()\n",
    "    if list[1].split('_')[1]=='days':\n",
    "        fro=to+relativedelta(days=-int(list[1].split('_')[0]))\n",
    "    if list[1].split('_')[1]=='months':\n",
    "        fro=to+relativedelta(months=-int(list[1].split('_')[0]))\n",
    "    if list[1].split('_')[1]=='years':\n",
    "        fro=to+relativedelta(years=-int(list[1].split('_')[0]))\n",
    "    to=to.strftime(\"%Y-%m-%d 00:00:00\")\n",
    "    fro=fro.strftime(\"%Y-%m-%d 00:00:00\")\n",
    "    item='a'\n",
    "    for i in list:\n",
    "        if i in ['coffee','Gov Tax','Taxi','Food','Clothings']:\n",
    "            item=i\n",
    "    return 'SELECT * FROM Statement WHERE customer_id = ',customer,' AND category = ',item, ' AND date < ',to,' AND date >= ',fro\n",
    "c=query1(entities)\n",
    "s= ''\n",
    "d=[s.join(c[0]),c[1],c[2],c[3],c[4],c[5],c[6],c[7],\"\"]\n",
    "x=[]\n",
    "for i in d:\n",
    "    x.append(i)\n",
    "s= \"'\"\n",
    "x=s.join(x)\n",
    "print(x)\n",
    "c=query1(entities)\n",
    "def sum(conn):\n",
    "    cur = conn.cursor()\n",
    "    cur.execute(x)\n",
    "    #cur.execute(\"SELECT * FROM statement WHERE customer_id='C1' AND category='Taxi' AND date <'2020-02-20 00:00:00' AND date>= '2019-10-20 00:00:00'\")\n",
    "    results=cur.fetchall()\n",
    "    #print(results[0][0])\n",
    "    return results\n",
    "#print(sum(connection))\n",
    "import pandas as pd\n",
    "df = pd.DataFrame.from_records(sum(connection))#,index=1)\n",
    "if df.size==0:\n",
    "    print(\"Oops...! Couldn't find any transactions matching this criteria\")\n",
    "if df.size!=0:\n",
    "    df.columns=['cust','date','amount','category']\n",
    "    df.date = pd.to_datetime(df.date)\n",
    "    dg = df.groupby(pd.Grouper(key='date', freq='1M')).sum() # groupby each 1 month\n",
    "    dg.index = dg.index.strftime('%b-%y')\n",
    "#Printing the output corresponding to the \n",
    "    if intent[0]=='how_much':\n",
    "        print('Hi Mr',customer,\",\")\n",
    "        print('The amount you spent in the last',entities[1].split('_')[0],entities[1].split('_')[1],'on',entities[0],'is',r,'dollars')\n",
    "        dg=dg[(dg.T != 0).any()]\n",
    "        print(dg)\n",
    "        #dg.plot.line()\n",
    "        #dg.plot.bar()\n",
    "        dg.plot.pie(subplots=True,autopct='%1.1f%%',shadow=True, startangle=90)\n",
    "    if intent[0]=='how_many':\n",
    "        df=df[[\"date\",\"amount\"]]\n",
    "        df.columns=['date','count']\n",
    "        df.date = pd.to_datetime(df.date)\n",
    "        dg = df.groupby(pd.Grouper(key='date', freq='1M')).count() # groupby each 1 month\n",
    "        dg.index = dg.index.strftime('%b-%y')\n",
    "        print('Hi Mr',customer)\n",
    "        print('The no. of times you spent in the last',entities[1].split('_')[0],entities[1].split('_')[1],'on',entities[0],'is',r)\n",
    "        print()\n",
    "        #dg.index.name=\"Month\"\n",
    "        dg.columns=['count']\n",
    "        dg=dg[(dg.T != 0).any()]\n",
    "        print(dg)\n",
    "        #dg.plot.line()\n",
    "        dg.plot.bar()\n",
    "        #dg.plot.pie(subplots=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hi C1 Hi,I didnot get what you are exactly looking for. \n",
      "Please,Enter what you require from: how_much spent or how_many times spent: how_much spent\n",
      "how_much\n"
     ]
    }
   ],
   "source": [
    "#Program for asking Intent\n",
    "inp = \"\"\n",
    "while inp != \"how_much spent\" and inp != \"how_many times spent\":\n",
    "    print('Hi',customer,'Hi,I didnot get what you are exactly looking for. ')\n",
    "    inp = input(\"Please,Enter what you require from: how_much spent or how_many times spent: \")\n",
    "    if inp != \"how_much spent\" and inp != \"how_many times spent\":\n",
    "        print(\"You must type how_much spent or how_many times spent\")\n",
    "intent[0]=inp.split(' ')[0]\n",
    "print(intent[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "entities=['8_months']#'Gov Tax','8_months']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Hi C1 Hi,I didnot get what you are exactly looking for. \n",
      "Please,Enter the category which you want to analyse: coffee ,Gov Tax ,Taxi ,Food ,Clothings: Taxi\n"
     ]
    }
   ],
   "source": [
    "#Program for asking entity1\n",
    "inp1 = \"\"\n",
    "while len(entities)!=2 and len([value for value in entities if value in ['coffee','Gov Tax','Taxi','Food','Clothings']])!=1:\n",
    "    print('Hi',customer,'Hi,I didnot get what you are exactly looking for. ')\n",
    "    inp1 = input(\"Please,Enter the category which you want to analyse: coffee ,Gov Tax ,Taxi ,Food ,Clothings: \")\n",
    "    if inp1 in ['coffee','Gov Tax','Taxi','Food','Clothings']:\n",
    "        entities.append(inp1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['8_months', 'Taxi']"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "entities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Taxi', '8_months']"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Rearranging the entities so thta entity one is the category\n",
    "#entities=['80_days','Gov Tax']\n",
    "def rearrange(list):\n",
    "    if list[0] in ['coffee','Gov Tax','Taxi','Food','Clothings']:\n",
    "        return list\n",
    "    if list[1] in ['coffee','Gov Tax','Taxi','Food','Clothings']:\n",
    "        temp=list[0]\n",
    "        list[0]=list[1]\n",
    "        list[1]=temp\n",
    "        return list\n",
    "rearrange(entities)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8_months\n"
     ]
    }
   ],
   "source": [
    "for i in entities:\n",
    "    if i.endswith(\"days\")==True or i.endswith(\"months\")==True or i.endswith(\"years\")==True:\n",
    "        print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['How', 'much', 'did', 'i', 'spent', 'on', 'clothing', 'in', 'the', 'last', 'one', 'month']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['How', 'spent', 'clothing', 'month']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x=\"How much did i spent on clothing in the last one month?\"\n",
    "from gensim.parsing.preprocessing import remove_stopwords\n",
    "punctuations = '''!()-[]{};:'\"\\,<>./?@#$%^&*_~'''\n",
    "for y in x.lower(): \n",
    "    if y in punctuations: \n",
    "        x = x.replace(y, \"\")\n",
    "print(x.split())\n",
    "x=remove_stopwords(x)\n",
    "x=x.split()\n",
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['how', 'spent', 'clothing', 'month']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y=[]\n",
    "for word in x:\n",
    "    word=word.lower()\n",
    "    y.append(word)\n",
    "y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['How', 'much', 'did', 'i', 'spent', 'on', 'clothing', 'in', 'the', 'last', 'one', 'month']\n",
      "['How', 'spent', 'clothing', 'month']\n"
     ]
    }
   ],
   "source": [
    "import spacy\n",
    "sp = spacy.load('en_core_web_sm')\n",
    "\n",
    "all_stopwords = sp.Defaults.stop_words\n",
    "\n",
    "x=\"How much did i spent on clothing in the last one month?\"\n",
    "from gensim.parsing.preprocessing import remove_stopwords\n",
    "punctuations = '''!()-[]{};:'\"\\,<>./?@#$%^&*_~'''\n",
    "for y in x.lower(): \n",
    "    if y in punctuations: \n",
    "        x = x.replace(y, \"\")\n",
    "print(x.split())\n",
    "x= [word for word in x.split() if not word in all_stopwords]\n",
    "\n",
    "print(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n"
     ]
    }
   ],
   "source": [
    "for i in range(10):\n",
    "    if i>=4:\n",
    "        print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "filename=\"statement\"\n",
    "con=sqlite3.connect(filename+\".db\")\n",
    "wb=pd.ExcelFile(filename+'.xlsx')\n",
    "for sheet in wb.sheet_names:\n",
    "        df=pd.read_excel(filename+'.xlsx',sheet_name=0)\n",
    "        df.to_sql(sheet,con, index=False,if_exists=\"replace\")\n",
    "con.commit()\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}