project20200131.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['C:\\\\Users\\\\yv84\\\\dev\\\\python\\\\sssss\\\\2020_c1_p1\\\\1.xlsx', 'C:\\\\Users\\\\yv84\\\\dev\\\\python\\\\sssss\\\\2020_c1_p1\\\\~$1.xlsx', 'C:\\\\Users\\\\yv84\\\\dev\\\\python\\\\sssss\\\\2020_c2_p1\\\\1.xlsx']\n",
      "C:\\Users\\yv84\\dev\\python\\sssss\\2020_c1_p1\\1.xlsx\n",
      "   ID   Контрагент   Проект       Дата Оплачено\n",
      "0   1  Контрагент1  Проект1 2020-01-31      NaN\n",
      "1   2  Контрагент1  Проект2 2020-01-20        +\n",
      "2   3  Контрагент2  Проект1 2020-02-26      NaN\n",
      "C:\\Users\\yv84\\dev\\python\\sssss\\2020_c2_p1\\1.xlsx\n",
      "   ID   Контрагент   Проект       Дата Оплачено\n",
      "0   1  Контрагент1  Проект1 2020-01-31      NaN\n",
      "1   2  Контрагент1  Проект2 2020-02-03      NaN\n",
      "2   3  Контрагент2  Проект1 2020-01-15        +\n",
      "Текущая дата: 2020-02-05 00:00:00\n",
      "Необходимо оплатить контрагенту 'Контрагент1' по проекту 'Проект1'. Дата платежа 2020-01-31 00:00:00\n",
      "Необходимо оплатить контрагенту 'Контрагент1' по проекту 'Проект1'. Дата платежа 2020-01-31 00:00:00\n",
      "Необходимо оплатить контрагенту 'Контрагент1' по проекту 'Проект2'. Дата платежа 2020-02-03 00:00:00\n"
     ]
    }
   ],
   "source": [
    "# Получаю в папке все файлы xlsx и по колонке Дата, смотрю, какие строки Дата + 5 дней, вывожу по ним сообщение\n",
    "\n",
    "\n",
    "import pandas\n",
    "import pandas as pd\n",
    "import numpy\n",
    "import numpy as np\n",
    "import ctypes\n",
    "import glob\n",
    "import codecs\n",
    "import datetime\n",
    "import time\n",
    "\n",
    "\n",
    "DIR = r\"C:\\Users\\yv84\\dev\\python\\sssss\\**\\*.xlsx\"\n",
    "\n",
    "id_column = \"ID\"\n",
    "contragent_column = \"Контрагент\"\n",
    "contragent_project = \"Проект\"\n",
    "payed_day_column = \"Дата\"\n",
    "column_payed = \"Оплачено\"\n",
    "\n",
    "\n",
    "def df_empty(columns, dtypes, index=None):\n",
    "    assert len(columns)==len(dtypes)\n",
    "    df = pd.DataFrame(index=index)\n",
    "    for c,d in zip(columns, dtypes):\n",
    "        df[c] = pd.Series(dtype=d)\n",
    "    return df\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def get_payments():\n",
    "    \n",
    "    df = df_empty(['ID', 'Контрагент', 'Проект', 'Дата', 'Оплачено'], \n",
    "              dtypes=[np.int64, np.str, numpy.str, numpy.datetime64, numpy.str])\n",
    "    all_xlsx = [df]\n",
    "    \n",
    "    files = glob.glob(DIR)\n",
    "    print(files)\n",
    "    for file in files:\n",
    "        # Временный файл\n",
    "        if \"~$\" in file:\n",
    "            continue\n",
    "        xlsx = pandas.read_excel(file)[[id_column, contragent_column, contragent_project, payed_day_column, column_payed]]\n",
    "        print(file)\n",
    "        print(xlsx)\n",
    "        all_xlsx.append(xlsx)\n",
    "\n",
    "    df = pd.concat(all_xlsx)\n",
    "\n",
    "    df_filtered = df[(df['Оплачено'] != \"+\")]\n",
    "    now_plus_5 = (datetime.datetime.now()  + datetime.timedelta(days=5)).replace(hour=0, minute=0, second=0, microsecond=0)\n",
    "    date_to = pd.Timestamp(now_plus_5, tz=None)\n",
    "    print(\"Текущая дата: \" + str(date_to))\n",
    "\n",
    "    # Убираем строки, которые отмечены, как оплаченные\n",
    "    df_filtered = df_filtered[(df_filtered['Дата'] < '2020-02-05')]\n",
    "    # Оставляем строки, дата которыех меньше текущей + 5 дней\n",
    "    df_filtered = df_filtered[(df_filtered['Дата'] < date_to)]\n",
    "    return df_filtered\n",
    "\n",
    "def show_windows_message(id_column, contragent_column, contragent_project, payed_day_column, column_payed):\n",
    "    message = \"Необходимо оплатить контрагенту '\" + contragent_column \\\n",
    "             + \"' по проекту '\" + contragent_project \\\n",
    "             + \"'. Дата платежа \" + payed_day_column\n",
    "    print(message)\n",
    "    ctypes.windll.user32.MessageBoxW(0, message, \"Напоминание\", 32)\n",
    "    \n",
    "\n",
    "#def reactor():\n",
    "#    while True:\n",
    "df = get_payments()\n",
    "for index, row in df.iterrows():\n",
    "    show_windows_message(row[id_column],\n",
    "                         row[contragent_column], \n",
    "                         row[contragent_project],\n",
    "                         str(row[payed_day_column]),\n",
    "                         row[column_payed])\n"
   ]
  },
  {
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}