Extract_from_PDF.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Path to strength test reports\n",
    "folder = r\"C:\\Users\\remaa\\Documents\\[Project-Personal]\\2017-2098 (Powell River)\\Concrete Strength Test\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pdfplumber, re, os, openpyxl, subprocess\n",
    "from openpyxl.styles import Font, Alignment, Border, Side\n",
    "from tqdm import tqdm\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib as mpl\n",
    "import numpy as np\n",
    "from fpdf import FPDF\n",
    "from itertools import zip_longest\n",
    "pd.options.mode.chained_assignment = None  # default='warn'\n",
    "\n",
    "files = [os.path.join(folder, file) for file in os.listdir(folder) if file.lower().endswith('.pdf')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Classes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class concrete_test():\n",
    "    def __init__(self, file):\n",
    "        self.file = file\n",
    "        with pdfplumber.open(file) as pdf:\n",
    "            data = []\n",
    "            for i in range(len(pdf.pages)):\n",
    "                page = pdf.pages[i]\n",
    "                data.append(page.extract_text())\n",
    "            self.data = \"\\n\".join(data)\n",
    "            \n",
    "\n",
    "        self.find_company()\n",
    "\n",
    "        self.test_data_cols = ['Specimen','Cure','Test_Date', 'Age', 'Compressive_Str']\n",
    "        self.extracted_data = {\n",
    "            \"Filename\": os.path.basename(file),\n",
    "            \"Filepath\": file,\n",
    "            \"Company\": self.company,\n",
    "            \"Report Date\": None,\n",
    "            \"Test Data\": pd.DataFrame(columns=self.test_data_cols),\n",
    "            \"Set Num\": None,\n",
    "            \"Specimens\": 0,\n",
    "            \"Cast Date\": None,\n",
    "            \"Transported Date\": None,\n",
    "            \"Specified Strength\": None,\n",
    "            \"Specified Strength Days\": None,\n",
    "            \"Admixtures\": [],\n",
    "            \"Mix Number\": None,\n",
    "            \"Load Vol\": None,\n",
    "            \"Slump\": None,\n",
    "            \"Specified Slump\": [],\n",
    "            \"Air\": None,\n",
    "            \"Specified Air\": [],\n",
    "            \"Location Comments\": [],\n",
    "            \"Other Comments\": [],\n",
    "            \"Report Date\": None,\n",
    "            \"Errors\": pd.DataFrame(columns=['Error Code', 'Description'])\n",
    "        }\n",
    "\n",
    "        if self.company:\n",
    "            if self.company == \"McElhanney\":\n",
    "                self.McElhanney()\n",
    "            if self.company == \"Kontur Geotechnical Consultants\":\n",
    "                self.Kontur()\n",
    "            self.check_errors()\n",
    "        else:\n",
    "            self.log_error('a',self.data.split('\\n')[0].strip())\n",
    "\n",
    "    def combine_sheets(self, combine):\n",
    "        for key in combine.keys():\n",
    "            if self.extracted_data['Mix Number'] in combine[key]:\n",
    "                self.extracted_data['Mix Number'] = key\n",
    "\n",
    "    def find_company(self):\n",
    "        company = None\n",
    "        if 'mcelhanney' in self.data.split('\\n')[0].lower():\n",
    "            company = \"McElhanney\"\n",
    "        if 'kontur' in self.data.split('\\n')[0].lower():\n",
    "            company = \"Kontur Geotechnical Consultants\"\n",
    "        self.company = company\n",
    "    \n",
    "    def log_error(self, code, desc):\n",
    "        df = pd.DataFrame([[code, desc]],columns = ['Error Code', 'Description'])\n",
    "        self.extracted_data['Errors'] = pd.concat([self.extracted_data['Errors'], df], ignore_index=True)\n",
    "\n",
    "    def McElhanney(self):\n",
    "        try:\n",
    "            pattern = {\n",
    "                \"Test Data\": r\".*([A-Z]) *Cylinder *(Lab|Field) *(\\d*) *((\\d{2}-\\w{3})-? *(\\d*) *[A-Za-z ]*)?\\d{3}.\\d *\\d{3}.\\d( *\\d* *(\\d*.\\d))?\",\n",
    "                \"Specified Strength\": r\"SPECIFIED STRENGTH\\s*(\\d*)MPa *@ *(\\d*) DAYS\",\n",
    "                \"Set Data\": r\".*SET *NO.(\\d*) *SPECIMENS *(\\d*) *CAST *(\\d{2}-\\w{3}-\\d{4}) *TRANSPORTED *(\\d{2}-\\w{3}-\\d{4})\",\n",
    "                \"Report Date\": r\".*Page *1 *of *\\d* *(\\d{4}.\\w{3}.\\d*)|(\\d*-\\w{3}-\\d{4})\",\n",
    "                \"Air\": r\".*AIR *(\\d{1,2}.?\\d?) *% *SPEC. *(\\d{1,2}.?\\d?) *± *(\\d{1,2}.?\\d?)\",\n",
    "                \"Slump\": r\".*SLUMP *(\\d*) *mm *SPEC. *(\\d*) *± *(\\d*)\",\n",
    "                \"Mix\": r\".*MIX *NO. *(.*)$\",\n",
    "                \"Load Volume\": r\"^LOAD *VOL. *(\\d*) m3\"\n",
    "            }\n",
    "            for i, line in enumerate(self.data.split('\\n')):\n",
    "                specified_strength = re.match(pattern['Specified Strength'], line.strip())\n",
    "                if specified_strength:\n",
    "                    self.extracted_data[\"Specified Strength\"] = int(specified_strength.group(1))\n",
    "                    self.extracted_data[\"Specified Strength Days\"] = int(specified_strength.group(2))\n",
    "\n",
    "                set_data = re.match(pattern[\"Set Data\"], line)\n",
    "                if set_data:\n",
    "                    self.extracted_data[\"Set Num\"] = set_data.group(1)\n",
    "                    self.extracted_data[\"Specimens\"] = int(set_data.group(2))\n",
    "                    self.extracted_data[\"Cast Date\"] = datetime.strptime(set_data.group(3),\"%d-%b-%Y\")\n",
    "                    self.extracted_data[\"Transported Date\"] = datetime.strptime(set_data.group(4),\"%d-%b-%Y\")\n",
    "                    continue\n",
    "\n",
    "                test_data = re.match(pattern[\"Test Data\"], line)\n",
    "                if test_data:\n",
    "                    data = [test_data.group(1), test_data.group(2), test_data.group(5), test_data.group(6), test_data.group(8)]\n",
    "                    if data[2]:\n",
    "                        if datetime.strptime(data[2] + \"-\" +str(datetime.now().year), \"%d-%b-%Y\") > datetime.now():\n",
    "                            data[2] = datetime.strptime(data[2] + \"-\" +str(datetime.now().year - 1), \"%d-%b-%Y\")\n",
    "                        else:\n",
    "                            data[2] = datetime.strptime(data[2] + \"-\" +str(datetime.now().year), \"%d-%b-%Y\")\n",
    "                    df = pd.DataFrame([data],columns = self.test_data_cols)\n",
    "                    self.extracted_data['Test Data'] = pd.concat([self.extracted_data['Test Data'], df], ignore_index=True)\n",
    "                    continue\n",
    "                \n",
    "                report_date = re.match(pattern['Report Date'], line)\n",
    "                if report_date:\n",
    "                    if report_date.group(1):\n",
    "                        self.extracted_data['Report Date'] = datetime.strptime(report_date.group(1),\"%Y.%b.%d\")\n",
    "                    else:\n",
    "                        self.extracted_data['Report Date'] = datetime.strptime(report_date.group(2),\"%d-%b-%Y\")\n",
    "                    continue\n",
    "\n",
    "                air = re.match(pattern['Air'], line)\n",
    "                if air:\n",
    "                    self.extracted_data['Air'] = float(air.group(1))\n",
    "                    self.extracted_data['Specified Air'] = [float(air.group(2))-float(air.group(3)), float(air.group(2))+float(air.group(3))]\n",
    "                    continue\n",
    "\n",
    "                if line.lower().strip().startswith('admixtures (ml/m'):\n",
    "                    for j in range(i, len(self.data.split('\\n'))):\n",
    "                        if self.data.split('\\n')[j].lower().strip().startswith('curing conditions'):\n",
    "                            self.extracted_data['Admixtures'] = self.data.split('\\n')[i+1:j]\n",
    "                            break\n",
    "                    continue\n",
    "\n",
    "                if line.lower().strip() == \"location\":\n",
    "                    for j in range(i, len(self.data.split('\\n'))):\n",
    "                        if self.data.split('\\n')[j].lower().strip().startswith('supplier'):\n",
    "                            self.extracted_data['Location Comments'] = self.data.split('\\n')[i+1:j]\n",
    "                            break\n",
    "                    continue\n",
    "\n",
    "                if line.lower().strip() == \"comments\":\n",
    "                    for j in range(i, len(self.data.split('\\n'))):\n",
    "                        if self.data.split('\\n')[j].lower().strip().startswith('load'):\n",
    "                            self.extracted_data['Other Comments'] = self.data.split('\\n')[i+1:j]\n",
    "                            break\n",
    "                    continue\n",
    "                \n",
    "                slump = re.match(pattern['Slump'], line)\n",
    "                if slump:\n",
    "                    self.extracted_data['Slump'] = int(slump.group(1))\n",
    "                    self.extracted_data['Specified Slump'] = [int(slump.group(2))-int(slump.group(3)),int(slump.group(2))+int(slump.group(3))]\n",
    "                    continue\n",
    "\n",
    "                mix = re.match(pattern['Mix'], line)\n",
    "                if mix:\n",
    "                    self.extracted_data['Mix Number'] = mix.group(1)\n",
    "                    continue\n",
    "\n",
    "                load = re.match(pattern['Load Volume'], line.strip())\n",
    "                if load:\n",
    "                    self.extracted_data['Load Vol'] = float(load.group(1))\n",
    "\n",
    "        except Exception as e:\n",
    "            self.log_error('z',str(e))\n",
    "\n",
    "    def Kontur(self):\n",
    "        try:\n",
    "            pattern = {\n",
    "                \"Test Data\": r\"([A-Z]) *Cylinder *(\\d*) *(Lab|Field) *((\\d{2}-\\w{3})|(\\w{3}.\\d{2})-? *(\\d*) *[A-Za-z ]*)?\\d{3}.\\d *\\d{3}.\\d( *\\d* *(\\d*.\\d))?\",\n",
    "                \"Specified Strength\": r\"SPECIFIED *STRENGTH: *(\\d*) *MPa *@ *(\\d*) * DAYS\",\n",
    "                \"Set Data\": r\"SET *NO.:(\\d*) *SPECIMENS: *(\\d*) *CAST: *(\\d{4}.\\w{3}.\\d{2}) *TRANSPORTED: *(\\d{4}.\\w{3}.\\d{2})\",\n",
    "                \"Report Date\": r\".*Page *1 *of *\\d* *(\\d*.\\w{3}.\\d*)|(\\d*-\\w{3}-\\d{4})\",\n",
    "                \"Air\": r\".*AIR: *(\\d{1,2}.?\\d?) *% *SPEC.: *(\\d{1,2}.?\\d?) *± *(\\d{1,2}.?\\d?)\",\n",
    "                \"Slump\": r\".*SLUMP: *(\\d*) *mm *SPEC.: *(\\d*) *± *(\\d*)\",\n",
    "                \"Mix\": r\".*MIX *NO.:? *(.*)$\",\n",
    "                \"Load Volume\": r\"^LOAD *VOL.: *(\\d*) m3\"\n",
    "            }\n",
    "            for i, line in enumerate(self.data.split('\\n')):\n",
    "                specified_strength = re.match(pattern['Specified Strength'], line.strip())\n",
    "                if specified_strength:\n",
    "                    self.extracted_data[\"Specified Strength\"] = int(specified_strength.group(1))\n",
    "                    self.extracted_data[\"Specified Strength Days\"] = int(specified_strength.group(2))\n",
    "\n",
    "                set_data = re.match(pattern[\"Set Data\"], line)\n",
    "                if set_data:\n",
    "                    self.extracted_data[\"Set Num\"] = set_data.group(1)\n",
    "                    self.extracted_data[\"Specimens\"] = int(set_data.group(2))\n",
    "                    self.extracted_data[\"Cast Date\"] = datetime.strptime(set_data.group(3),\"%Y.%b.%d\")\n",
    "                    self.extracted_data[\"Transported Date\"] = datetime.strptime(set_data.group(4),\"%Y.%b.%d\")\n",
    "                    continue\n",
    "\n",
    "                test_data = re.match(pattern[\"Test Data\"], line)\n",
    "                if test_data:\n",
    "                    data = [test_data.group(1), test_data.group(3), test_data.group(6), test_data.group(7), test_data.group(9)]\n",
    "                    if data[2]:\n",
    "                        if datetime.strptime(data[2] + \"-\" +str(datetime.now().year), \"%b.%d-%Y\") > datetime.now():\n",
    "                            data[2] = datetime.strptime(data[2] + \"-\" +str(datetime.now().year - 1), \"%b.%d-%Y\")\n",
    "                        else:\n",
    "                            data[2] = datetime.strptime(data[2] + \"-\" +str(datetime.now().year), \"%b.%d-%Y\")\n",
    "                    df = pd.DataFrame([data],columns = self.test_data_cols)\n",
    "                    self.extracted_data['Test Data'] = pd.concat([self.extracted_data['Test Data'], df], ignore_index=True)\n",
    "                    continue\n",
    "                \n",
    "                report_date = re.match(pattern['Report Date'], line)\n",
    "                if report_date:\n",
    "                    if report_date.group(1):\n",
    "                        self.extracted_data['Report Date'] = datetime.strptime(report_date.group(1),\"%Y.%b.%d\")\n",
    "                    else:\n",
    "                        self.extracted_data['Report Date'] = datetime.strptime(report_date.group(2),\"%d-%b-%Y\")\n",
    "                    continue\n",
    "\n",
    "                air = re.match(pattern['Air'], line)\n",
    "                if air:\n",
    "                    self.extracted_data['Air'] = float(air.group(1))\n",
    "                    self.extracted_data['Specified Air'] = [float(air.group(2))-float(air.group(3)), float(air.group(2))+float(air.group(3))]\n",
    "                    continue\n",
    "\n",
    "                if line.lower().strip().startswith('admixtures'):\n",
    "                    for j in range(i, len(self.data.split('\\n'))):\n",
    "                        if self.data.split('\\n')[j].lower().strip().startswith('curing'):\n",
    "                            self.extracted_data['Admixtures'] = self.data.split('\\n')[i+1:j]\n",
    "                            break\n",
    "                    continue\n",
    "\n",
    "                if line.lower().strip() == \"location:\":\n",
    "                    for j in range(i, len(self.data.split('\\n'))):\n",
    "                        if self.data.split('\\n')[j].lower().strip().startswith('supplier'):\n",
    "                            self.extracted_data['Location Comments'] = self.data.split('\\n')[i+1:j]\n",
    "                            break\n",
    "                    continue\n",
    "\n",
    "                if line.lower().strip() == \"comments:\":\n",
    "                    for j in range(i, len(self.data.split('\\n'))):\n",
    "                        if self.data.split('\\n')[j].lower().strip().startswith('load'):\n",
    "                            self.extracted_data['Other Comments'] = self.data.split('\\n')[i+1:j]\n",
    "                            break\n",
    "                    continue\n",
    "                \n",
    "                slump = re.match(pattern['Slump'], line)\n",
    "                if slump:\n",
    "                    self.extracted_data['Slump'] = int(slump.group(1))\n",
    "                    try:\n",
    "                        self.extracted_data['Specified Slump'] = [int(slump.group(2))-int(slump.group(3)),int(slump.group(2))+int(slump.group(3))]\n",
    "                    except:\n",
    "                        self.extracted_data['Specified Slump'] = []\n",
    "                    continue\n",
    "\n",
    "                mix = re.match(pattern['Mix'], line)\n",
    "                if mix:\n",
    "                    self.extracted_data['Mix Number'] = mix.group(1)\n",
    "                    continue\n",
    "\n",
    "                load = re.match(pattern['Load Volume'], line.strip())\n",
    "                if load:\n",
    "                    try:\n",
    "                        self.extracted_data['Load Vol'] = float(load.group(1))\n",
    "                    except:\n",
    "                        self.log_error('e', 'Missing information on Load volume')\n",
    "\n",
    "        except Exception as e:\n",
    "            self.log_error('z',str(e))\n",
    "\n",
    "    def check_errors(self):\n",
    "        self.err = {\n",
    "            'a':'Template for this company is not defined',\n",
    "            'b': 'Specimen # in extracted PDF does not match scrapped test data from PDF',\n",
    "            'c': 'Slump issue',\n",
    "            'd': 'Air % Issue',\n",
    "            'e': 'Missing Information',\n",
    "            'z': 'Some other error'\n",
    "        }\n",
    "        data = self.extracted_data\n",
    "        if len(data['Test Data']) != data['Specimens']:\n",
    "            self.log_error('b', f\"Specimens: {data['Specimens']}, Avail. Data: {len(data['Test Data'])}\")\n",
    "\n",
    "        if data['Air'] and data['Specified Air'] != []:\n",
    "            if data['Air'] > data['Specified Air'][1]:\n",
    "                self.log_error('d', f\"Air: {data['Air']}% > Allow. max:{data['Specified Air'][1]}%\")\n",
    "            elif data['Air'] < data['Specified Air'][0]:\n",
    "                self.log_error('d', f\"Air: {data['Air']}% < Allow. min:{data['Specified Air'][0]}%\")\n",
    "        else:\n",
    "            self.log_error('e', 'No Air content data found.')\n",
    "\n",
    "        if data['Slump'] and data['Specified Slump'] != []:\n",
    "            if data['Slump'] > data['Specified Slump'][1]:\n",
    "                self.log_error('c', f\"Slump: {data['Slump']}mm > Allow. max:{data['Specified Slump'][1]}mm\")\n",
    "            elif data['Slump'] < data['Specified Slump'][0]:\n",
    "                self.log_error('c', f\"Slump: {data['Slump']}mm <  Allow. min:{data['Specified Slump'][0]}mm\")\n",
    "        else:\n",
    "            self.log_error('e', 'Slump info not found.')\n",
    "        self.extracted_data = data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class xlsx():\n",
    "    def __init__(self, file):\n",
    "        self.wb = openpyxl.Workbook()\n",
    "        self.wb.active.title = \"Summary\"\n",
    "        self.file = file\n",
    "        self.saved = False\n",
    "        self.define_styles()\n",
    "\n",
    "    def define_styles(self):\n",
    "        self.fonts = {\n",
    "            'h1': Font(name='Arial', size=12, italic=False, bold=True, color='FF000000', strike=False,underline='none',vertAlign=None)\n",
    "        }\n",
    "\n",
    "        self.alignments = {\n",
    "            'center':Alignment(horizontal='center', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0)\n",
    "        }\n",
    "\n",
    "        self.borderthickness = {\n",
    "            'thin': Side(border_style=\"thin\", color=\"000000\"),\n",
    "            'double': Side(border_style=\"double\", color=\"000000\"),\n",
    "            'thick': Side(border_style=\"thick\", color=\"000000\")\n",
    "        }\n",
    "    def write_excel_data(self, ws, data, start_row, start_col = 1):\n",
    "        \"\"\"Writes a array of array to excel worksheet\n",
    "\n",
    "        Args:\n",
    "            ws (Openpyxl Worksheet Class): Sheet to which the data is written\n",
    "            data (Array of Arrays): Data to write in array of array format; [[R1C1, R1C2],[R2C1, R2C2]]\n",
    "            start_row (int): Start row; 1-indexed;Excel row to write to\n",
    "        \"\"\"\n",
    "        #print(f\"Writing {len(data)} rows of data to {ws}, starting at {start_row}\")\n",
    "        for i in range(start_row, start_row + len(data)):\n",
    "            for j in range(start_col + len(data[i-start_row]) - 1):                \n",
    "                cell = (openpyxl.utils.get_column_letter(1+j)) + str(i)\n",
    "                ws[cell] = data[i-start_row][j]\n",
    "                self.saved = False\n",
    "        \n",
    "        return start_row + len(data)\n",
    "\n",
    "    def draw_border(self, ws, data, start_row, start_col=1):\n",
    "        max_rows = len(data) + start_row\n",
    "        max_cols = start_col\n",
    "\n",
    "        for row in data:\n",
    "            max_cols = max(max_cols, len(row)+start_col)\n",
    "\n",
    "        for i in range(start_row, max_rows):\n",
    "            for j in range(start_col-1, max_cols-1):\n",
    "                cell = openpyxl.utils.get_column_letter(j+1) + str(i)\n",
    "                left = self.borderthickness['thin']\n",
    "                right = self.borderthickness['thin']\n",
    "                top = self.borderthickness['thin']\n",
    "                bottom = self.borderthickness['thin']\n",
    "                if i == start_row:\n",
    "                    top = self.borderthickness['thick']\n",
    "                if i == max_rows-1:\n",
    "                    bottom = self.borderthickness['thick']\n",
    "                if j == start_col-1:\n",
    "                    left = self.borderthickness['thick']\n",
    "                if j == max_cols-2:\n",
    "                    right = self.borderthickness['thick']\n",
    "                \n",
    "                ws[cell].border = Border(left=left, right=right, top=top, bottom=bottom)\n",
    "                ws[cell].alignment = Alignment(wrap_text=True, vertical=\"center\")\n",
    "\n",
    "    def create_data_sheet(self, title, sample_data):\n",
    "        ws = self.wb.create_sheet(index=0, title=title)\n",
    "        self.saved = False\n",
    "        data = [\n",
    "            ['Powell River WWTP'],\n",
    "            ['Concrete Compressive Strength Summary'],\n",
    "            [f\"{sample_data['Mix Number']}: {sample_data['Specified Strength']}MPa @ {sample_data['Specified Strength Days']} days\"],\n",
    "            ['']\n",
    "        ]\n",
    "        next_row = self.write_excel_data(ws, data, 1)\n",
    "        data = [\n",
    "            ['Set', 'Cylinder', 'Description', 'Date', 'Date', 'Date', 'Age', 'Test', r'% of', 'Air', 'Slump', 'Comments'],\n",
    "            ['#', 'ID', '', 'Cast', 'Received', 'Tested', 'Days', 'MPa', 'Design', '%', 'mm', '']\n",
    "        ]\n",
    "        self.draw_border(ws, data,next_row)\n",
    "        next_row = self.write_excel_data(ws, data, next_row)\n",
    "\n",
    "        ws.column_dimensions[\"C\"].width = 30\n",
    "        ws.column_dimensions[\"D\"].width = 15\n",
    "        ws.column_dimensions[\"E\"].width = 15\n",
    "        ws.column_dimensions[\"F\"].width = 15\n",
    "        ws.column_dimensions[\"L\"].width = 30\n",
    "        \n",
    "        for cells in ws[\"1:3\"]:\n",
    "            for cell in cells:\n",
    "                cell.font = self.fonts['h1']\n",
    "        for cells in ws[\"4:6\"]:\n",
    "            for cell in cells:\n",
    "                cell.alignment = self.alignments['center']\n",
    "\n",
    "        ws.freeze_panes = 'A7'\n",
    "        return ws, next_row\n",
    "\n",
    "    def write_set_data(self, ws, data, startrow,startcol=1):\n",
    "        data_to_write = []\n",
    "\n",
    "        for index, row in data['Test Data'].iterrows():\n",
    "            if index == 0:\n",
    "                location_comment = [x.strip().title() for x in data['Location Comments']]\n",
    "                other_comment = [x.strip().title() for x in data['Other Comments']]\n",
    "                data_to_write.append([\n",
    "                    data['Set Num'], \n",
    "                    row['Specimen'], \n",
    "                    '\\n'.join(location_comment),\n",
    "                    datetime.strftime(data['Cast Date'], \"%d-%B-%Y\"),\n",
    "                    datetime.strftime(data['Transported Date'], \"%d-%B-%Y\"),\n",
    "                    datetime.strftime(row['Test_Date'], \"%d-%B-%Y\"),\n",
    "                    int(row['Age']) if row['Age'] != None else None,\n",
    "                    float(row['Compressive_Str']) if row['Compressive_Str'] != None else None,\n",
    "                    round(float(row['Compressive_Str']) / data['Specified Strength'],2) if row['Compressive_Str'] != None else None,\n",
    "                    data['Air'],\n",
    "                    data['Slump'],\n",
    "                    '\\n'.join(other_comment)\n",
    "                    ])\n",
    "            else:\n",
    "                data_to_write.append([\n",
    "                    '', '', '', '', '', '',\n",
    "                    int(row['Age']) if row['Age'] != None else None,\n",
    "                    float(row['Compressive_Str']) if row['Compressive_Str'] != None else None,\n",
    "                    round(float(row['Compressive_Str']) / data['Specified Strength'],2) if row['Compressive_Str'] != None else None,\n",
    "                ])\n",
    "\n",
    "\n",
    "        next_row = self.write_excel_data(ws, data_to_write, startrow)\n",
    "        self.draw_border(ws, data_to_write, startrow)\n",
    "        return next_row\n",
    "\n",
    "    def plot_sheet(self, sets, filename):\n",
    "        x_axis = []\n",
    "        req =[]\n",
    "        str_days = []\n",
    "        for set_key in sorted(sets.keys()):\n",
    "            for index, row in sets[set_key].extracted_data['Test Data'].iterrows():\n",
    "                if row['Compressive_Str'] and int(row['Age']) <= sets[set_key].extracted_data['Specified Strength Days']: \n",
    "                        if not int(row['Age']) in str_days:\n",
    "                            str_days.append(int(row['Age']))\n",
    "\n",
    "        df = pd.DataFrame(columns=sorted(str_days))\n",
    "\n",
    "        for set_key in sorted(sets.keys()):\n",
    "            #Create dataframe with setwise data      \n",
    "            df.loc[set_key] = 0  \n",
    "            for index, row in sets[set_key].extracted_data['Test Data'].iterrows():\n",
    "                if row['Compressive_Str'] and int(row['Age']) <= sets[set_key].extracted_data['Specified Strength Days']:\n",
    "                        df[int(row['Age'])][set_key] = float(row['Compressive_Str'])\n",
    "\n",
    "            str_days = sorted(str_days)\n",
    "            x_axis.append(str(set_key))\n",
    "            req.append(sets[set_key].extracted_data['Specified Strength'])\n",
    "\n",
    "        plt.clf()\n",
    "        plt.bar(x_axis, req, color='r', label='Target Str.')\n",
    "        plt.axhline(y=req[0],linewidth=1, color='r', linestyle='--')\n",
    "        c = np.arange(1, len(str_days) + 1)\n",
    "        norm = mpl.colors.Normalize(vmin=c.min(), vmax=c.max())\n",
    "        cmap = mpl.cm.ScalarMappable(norm=norm, cmap=mpl.cm.Blues)\n",
    "        cmap.set_array([])\n",
    "        i=0\n",
    "        for index, row in df.transpose().iterrows():\n",
    "            plt.bar(x_axis, row.to_list(), width=0.8, color=cmap.to_rgba(i + 1), alpha=1, label=f\"{row.name} days\")\n",
    "            i+=1\n",
    "        plt.legend(bbox_to_anchor=(1.04,1), loc=\"upper left\")\n",
    "        plt.xlabel('Set #')\n",
    "        plt.ylabel('MPa')\n",
    "        plt.title(\"How to read: You shouldn't see any red bars in graph\", fontsize=8)\n",
    "        \n",
    "        target={'str':sets[set_key].extracted_data['Specified Strength'], 'day': sets[set_key].extracted_data['Specified Strength Days']}\n",
    "        plt.suptitle(f\"{sets[set_key].extracted_data['Mix Number']}: {target['str']}MPa @ {target['day']} days test summary\")\n",
    "        plt.savefig(filename,bbox_inches='tight')\n",
    "        return filename\n",
    "        #plt.plot()\n",
    "\n",
    "    def save(self):\n",
    "        self.wb.save(self.file)\n",
    "        self.saved = True\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "class PDF(FPDF):\n",
    "    def header(self):\n",
    "        # Logo\n",
    "        self.image(r\"img_ae_logo_c_230.png\", 10, 8, 33)\n",
    "        # Arial bold 15\n",
    "        self.set_font('Times', 'B', 20)\n",
    "        # Move to the right\n",
    "        self.cell(80)\n",
    "        # Title\n",
    "        self.cell(30, 10, 'Concrete Test Report Summary', 0, 0, 'C')\n",
    "        self.cell(80)\n",
    "        self.set_font('Times', '', 11)\n",
    "        self.cell(0, 4, 'Arun Kishore', 0, 1, 'R')\n",
    "        self.cell(0, 4, datetime.now().strftime('%B %d, %Y'), 0, 0, 'R')\n",
    "        # Line break\n",
    "        self.ln(20)\n",
    "\n",
    "    # Page footer\n",
    "    def footer(self):\n",
    "        # Position at 1.5 cm from bottom\n",
    "        self.set_y(-15)\n",
    "        # Arial italic 8\n",
    "        self.set_font('Times', 'I', 8)\n",
    "        # Page number\n",
    "        self.cell(0, 10, 'Page ' + str(self.page_no()) + '/{nb}', 0, 0, 'C')\n",
    "\n",
    "    def draw_table(self, header, data, cell_width, heading='', subheading=''):\n",
    "        \"\"\"Draws a table in PDF based on provided input\n",
    "\n",
    "        Args:\n",
    "            pdf (<FPDF class>): _description_\n",
    "            header (tuple): A tuple with header row values\n",
    "            data (Array of Arrays): Array with table data\n",
    "            cell_width (Array): Array of int with col widths\n",
    "            heading (str, optional): Table Title. Defaults to ''.\n",
    "            subheading (str, optional): Table subtitle. Defaults to ''.\n",
    "        \"\"\"\n",
    "        #print(header)\n",
    "        #print(data)\n",
    "        effective_page_width = self.w - 2*self.l_margin\n",
    "        cell_offset = (effective_page_width - sum(cell_width))/2\n",
    "\n",
    "        if heading != '':\n",
    "            self.set_font('Times', 'B', 14)\n",
    "            self.cell(w=0, h=5, txt=heading, ln=1, align='C')\n",
    "\n",
    "        if subheading != '':\n",
    "            self.set_font('Times', 'I', 10)\n",
    "            self.cell(w=0, h=4, txt=subheading, ln=1, align='C')\n",
    "\n",
    "        if cell_offset > 0:\n",
    "            self.cell(cell_offset)\n",
    "\n",
    "        self.set_font('Times', 'B', 10)\n",
    "        for i, datum in enumerate(header):\n",
    "            self.cell(cell_width[i], self.font_size * 1.5, datum, border=1,align='C')\n",
    "        self.ln(self.font_size * 1.5)\n",
    "\n",
    "        self.set_font('Times', '', 10)\n",
    "        for row in data:\n",
    "            if cell_offset > 0:\n",
    "                self.cell(cell_offset)\n",
    "            for i, datum in enumerate(row):\n",
    "                self.cell(cell_width[i], self.font_size * 1.5, str(datum), border=1,align='C')\n",
    "            self.ln(self.font_size * 1.5)\n",
    "        self.ln(self.font_size * 1.5)\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Process PDF Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "parsed_data = []\n",
    "combine = {\n",
    "    'W-1': ['W-1 180', 'W1-150', 'W-1 (200)', 'W-1(32MPA)', 'W1'],\n",
    "    'W-2': ['W2', 'W-2-PEA'],\n",
    "    'W-3': ['W3'],\n",
    "    'W-4': ['W4'],\n",
    "    'W-5': ['W5'],\n",
    "    'W-6': ['W6', 'W-6 PEA', 'W-6(56)', 'W-6(32MPA)'],\n",
    "    'W-7': ['W-72']\n",
    "}\n",
    "\n",
    "for file in tqdm(files, desc=\"Prcessing PDFs\",unit=\"files\", colour=\"green\",):\n",
    "    data = concrete_test(file)\n",
    "    data.combine_sheets(combine)\n",
    "    parsed_data.append(data)\n",
    "\n",
    "sheets = {}\n",
    "for item in parsed_data:\n",
    "    if not item.extracted_data['Mix Number'] in sheets.keys():\n",
    "        sheets[item.extracted_data['Mix Number']] = item\n",
    "\n",
    "print(\"Following are the list of sheets that will be currently produced\")\n",
    "print(list(sheets.keys()))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## User Input Needed - Combine sheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "combine = {\n",
    "    'W-1': ['W-1 180', 'W1-150', 'W-1 (200)', 'W-1(32MPA)', 'W1'],\n",
    "    'W-2': ['W2', 'W-2-PEA', 'W2 (165)'],\n",
    "    'W-3': ['W3'],\n",
    "    'W-4': ['W4'],\n",
    "    'W-5': ['W5'],\n",
    "    'W-6': ['W6', 'W-6 PEA', 'W-6(56)', 'W-6(32MPA)'],\n",
    "    'W-7': ['W-72']\n",
    "}\n",
    "for file in parsed_data:\n",
    "    file.combine_sheets(combine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### User Input Needed - Define the strength requirements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Compile a list of sheets\n",
    "sheets = {}\n",
    "for item in parsed_data:\n",
    "    if not item.extracted_data['Mix Number'] in sheets.keys():\n",
    "        if item.extracted_data['Mix Number']:\n",
    "            sheets[item.extracted_data['Mix Number']] = \"\"\n",
    "\n",
    "specified = {\n",
    "    'W-1': [35,56, 'Watertight Concrete'],\n",
    "    'W-2': [35, 28, 'Exterior Structural Concrete'],\n",
    "    'W-3': [35, 28, 'Interior Structural Concrete'],\n",
    "    'W-4': [32, 56, 'Large Manhole Outfall'],\n",
    "    'W-5': [15, 56, 'Lean Concrete'],\n",
    "    'W-6': [32, 56, 'Bioreactor tank base slab'],\n",
    "    'W-7': [25, 56, 'Manhole and Footings'],\n",
    "    '11 BLOCK F': [25, 28, 'Masonry Mix']\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Report Generation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Update the sample data in sheets dict to point to corrent strength item\n",
    "for sheet in sheets.keys():\n",
    "    for item in parsed_data:\n",
    "        data = item.extracted_data\n",
    "        if data['Mix Number'] == sheet:\n",
    "            item.extracted_data['Specified Strength'] = specified[sheet][0]\n",
    "            item.extracted_data['Specified Strength Days'] = specified[sheet][1]\n",
    "            sheets[sheet] = item\n",
    "\n",
    "\n",
    "xl_file  = os.path.join(folder, os.path.basename(folder) + '.xlsx')\n",
    "xl = xlsx(xl_file)\n",
    "for sheet in sheets.keys():\n",
    "    if sheet == None:\n",
    "        continue\n",
    "    ws, current_row = xl.create_data_sheet(sheet, sheets[sheet].extracted_data)\n",
    "    \n",
    "    sets = {}\n",
    "\n",
    "    for item in parsed_data:\n",
    "        if item.extracted_data['Mix Number'] != sheet:\n",
    "            continue\n",
    "        if item.extracted_data['Set Num'] == None:\n",
    "            continue\n",
    "        set_num = int(item.extracted_data['Set Num'])\n",
    "        report_date = item.extracted_data['Report Date']\n",
    "        if not set_num in sets.keys():\n",
    "            sets[set_num] = item\n",
    "        elif len(item.extracted_data['Test Data'].dropna()) > len(sets[set_num].extracted_data['Test Data'].dropna()):\n",
    "            sets[set_num] = item\n",
    "\n",
    "    for set_key in sorted(sets.keys()):\n",
    "        current_row = xl.write_set_data(ws, sets[set_key].extracted_data, current_row+1)\n",
    "    \n",
    "    sheets[sheet].figure = xl.plot_sheet(sets, sheet + '.png')\n",
    "xl.save()\n",
    "print(f\"Generated excel file is saved at: {xl.file}\")\n",
    "print(f\"{len(sheets.keys())} plot files are also generated and saved in the above folder\")\n",
    "#subprocess.Popen('example_copy.xlsx', shell=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create PDF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pdf = PDF()\n",
    "pdf.alias_nb_pages()\n",
    "pdf.add_page()\n",
    "effective_page_width = pdf.w - 2*pdf.l_margin\n",
    "\n",
    "template_excluded_pdf = [x for x in parsed_data if len(x.extracted_data['Errors'][x.extracted_data['Errors']['Error Code'] == 'a']) > 0]\n",
    "missing_info_pdf = [x for x in parsed_data if len(x.extracted_data['Errors'][x.extracted_data['Errors']['Error Code'] == 'e']) > 0]\n",
    "other_err_pdf = [x for x in parsed_data if len(x.extracted_data['Errors'][x.extracted_data['Errors']['Error Code'] == 'e']) > 0]\n",
    "slump_pdf = [x for x in parsed_data if len(x.extracted_data['Errors'][x.extracted_data['Errors']['Error Code'] == 'c']) > 0]\n",
    "air_pdf = [x for x in parsed_data if len(x.extracted_data['Errors'][x.extracted_data['Errors']['Error Code'] == 'd']) > 0]\n",
    "summary = f\"\"\"\n",
    "The below summary is based on information from concrete test result PDFs located at: {folder}.\n",
    "\n",
    "A total of {len(files)} PDFs were considered.\"\"\"\n",
    "\n",
    "if len(template_excluded_pdf) > 0 or len(missing_info_pdf) > 0:\n",
    "    summary += f\" Out of which:\\n\"\n",
    "    if len(template_excluded_pdf) > 0:\n",
    "        summary += f\"\\t- {len(template_excluded_pdf)} were excluded since they didn't meet the defined template criteria.\\n\"\n",
    "    if len(missing_info_pdf) > 0:\n",
    "        summary += f\"\\t- {len(missing_info_pdf)} were found to have some missing information.\\n\"\"\"\n",
    "\n",
    "if len(air_pdf) > 0 or len(slump_pdf) > 0:\n",
    "    summary += f\"\\nFrom the supplied PDFs:\\n\"\n",
    "    if len(air_pdf) > 0:\n",
    "        summary += f\"\\t- {len(air_pdf)} were found to have air content outside of specified range.\\n\"\n",
    "    if len(slump_pdf) > 0:\n",
    "        summary += f\"\\t- {len(slump_pdf)} were found to have slump outside of specified range.\\n\"\n",
    "\n",
    "\n",
    "    summary += f\"Possible explanation for the above:\\n\"\n",
    "    if len(air_pdf) > 0:\n",
    "        summary += f\"\\t- For Air content, since we have multiple PDFs relating to a single set (7-day, 14-day,...) multiple PDFs flagged for violation might all pertain to a small # of samples.\\n\"\n",
    "    if len(slump_pdf) > 0:\n",
    "        summary += f\"\\t- For Slump, it is possible that the specified range is before the addition of superplasticizer but the reported range might have been after the addition.\\n\"\n",
    "    \n",
    "if len(template_excluded_pdf) > 0:\n",
    "    summary += f\"\"\"\\nA list of exluded files has been listed below.\\nIt is possible that a few of these files were rejected due to minor formatting differences, but if a large number of false negative is observed, contact Arun to redefine the templates to account for the changes.\n",
    "    \"\"\"\n",
    "\n",
    "#Summary\n",
    "pdf.set_font('Times', 'B', 18)\n",
    "#pdf.cell(w=0, h=10, txt='Summary:', border=0, ln=1, fill=False)\n",
    "pdf.cell(w=0, h=10, txt='Summary', border=0, ln=1, fill=False, align='C')\n",
    "pdf.set_font('Times', '', 12)\n",
    "pdf.multi_cell(w=0, h=6, txt=summary, border=0, fill=False)\n",
    "pdf.ln(pdf.font_size * 2.5)\n",
    "\n",
    "if len(template_excluded_pdf) > 0:\n",
    "    #Excluded Files\n",
    "    data = []\n",
    "    for file in template_excluded_pdf:\n",
    "        data.append([file.extracted_data['Filename']])\n",
    "\n",
    "    pdf.draw_table(\n",
    "        header= ['Filename'],\n",
    "        data = data,\n",
    "        cell_width= [2*effective_page_width / 3],\n",
    "        heading= 'Excluded Files',\n",
    "        subheading= 'List of files excluded from analysis since template does not match definied templates'\n",
    "    )\n",
    "\n",
    "if len(missing_info_pdf) > 0:\n",
    "    #Missing Information\n",
    "    pdf.add_page()\n",
    "    data = []\n",
    "    for file in missing_info_pdf:\n",
    "        issue = \"\"\n",
    "        err_desc = file.extracted_data['Errors'][file.extracted_data['Errors']['Error Code'] == 'e']['Description'].to_list()\n",
    "        if 'No Air content data found.' in err_desc:\n",
    "            issue = \"Air content\"\n",
    "        if 'Slump info not found.' in err_desc:\n",
    "            if issue != \"\":\n",
    "                issue += \", \"\n",
    "            issue += \"Slump\"\n",
    "        if 'Missing information on Load volume' in err_desc:\n",
    "            if issue != \"\":\n",
    "                issue += \", \"\n",
    "            issue += \"Load vol\"\n",
    "        data.append([file.extracted_data['Filename'], issue])\n",
    "\n",
    "    pdf.draw_table(\n",
    "        header= ('Filename', 'Missing Info'),\n",
    "        data = data,\n",
    "        cell_width= (2*effective_page_width / 3 - 20, effective_page_width / 3 - 10),\n",
    "        heading= 'Files with missing information',\n",
    "        subheading= 'List of files where specific information was noted to be missing'\n",
    "    )\n",
    "\n",
    "pdf.add_page()\n",
    "pdf.set_font('Times', 'B', 18)\n",
    "pdf.cell(w=0, h=10, txt='Target strength vs Achived strength', border=0, ln=1, fill=False, align='C')\n",
    "pdf.set_font('Times', '', 12)\n",
    "pdf.multi_cell(w=0, h=6, txt='The charts plot the target strengths vs achieved strengths.', border=0, fill=False, align='C')\n",
    "pdf.ln(pdf.font_size * 1.5)\n",
    "for sh in sheets.keys():\n",
    "    pdf.cell(20)\n",
    "    pdf.image(sheets[sh].figure)\n",
    "    pdf.ln(pdf.font_size * 2.5)\n",
    "\n",
    "if len(air_pdf) > 0 or len(slump_pdf) > 0:\n",
    "    #Air content Exceeded\n",
    "    pdf.add_page()\n",
    "    air_data = []\n",
    "    for file in air_pdf:\n",
    "        air_data.append([file.extracted_data['Filename']])\n",
    "    slump_data = []\n",
    "    for file in slump_pdf:\n",
    "        slump_data.append([file.extracted_data['Filename']])\n",
    "        \n",
    "    pdf.draw_table(\n",
    "        header= ['Missing Air %', 'Missing Slump'],\n",
    "        data = zip_longest(air_data, slump_data),\n",
    "        cell_width= [effective_page_width / 2, effective_page_width / 2],\n",
    "        heading= 'Files where Air content or slump falls outside specified range',\n",
    "        subheading= 'See summary for a brief description and possible explanation for below list of files'\n",
    "    )\n",
    "\n",
    "pdf.file = os.path.join(folder, os.path.basename(folder) + '.pdf')\n",
    "pdf.output(pdf.file, 'F')\n",
    "print(f\"PDF file can be located at: {pdf.file}\")\n",
    "subprocess.Popen(pdf.file, shell=True)\n",
    "\n",
    "del pdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finish"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for sh in sheets.keys():\n",
    "    try:\n",
    "        os.remove(sheets[sh].figure)\n",
    "        print(f\"File removed: {sheets[sh].figure}\")\n",
    "    except Exception as e:\n",
    "        print(e)\n",
    "\n",
    "for file in parsed_data:\n",
    "    del file\n",
    "parsed_data = []\n",
    "print(\"All data flushed from memory\")"
   ]
  }
 ],
 "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.10.2"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "e13bd5fa926c90393b4a6a97f562b5d67c2d1d2dc8ae27959fac6f1a2eab2b2d"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}