{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Search Granta MI\n",
    "Use the three available search methods to search for records."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All three methods can be performed at either the Session, Database, or Table levels:\n",
    "\n",
    "* Simple text search\n",
    "* Record name search\n",
    "* Criteria search"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect to MI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from GRANTA_MIScriptingToolkit import granta as mpy\n",
    "mi = mpy.connect('http://localhost/mi_servicelayer', autologon=True)\n",
    "db = mi.get_db(db_key='MI_Training')\n",
    "material_universe = db.get_table('MaterialUniverse', unit_system='UK Imperial', absolute_temperatures=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Simple text search\n",
    "Search for records which contain the string anywhere in the record."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:Low alloy steel, AISI 4130, air melted, normalized>]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "normalized_material_records = material_universe.search_for_records_by_text('Normalized')\n",
    "normalized_material_records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Record name search\n",
    "Search for records by name. Only exact matches to the long or short name of a record are returned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:Soda barium glass>]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sodium_barium_glass_records = material_universe.search_for_records_by_name('Soda barium glass')\n",
    "sodium_barium_glass_records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Criteria searches\n",
    "Search criteria are constructed from **AttributeDefinition** objects using the `search_criterion()` method.\n",
    "\n",
    "This search type is typically used in 7 different scenarios:\n",
    "\n",
    "* Text searches\n",
    "* Numeric searches\n",
    "* Date searches\n",
    "* Boolean searches\n",
    "* Tabular searches\n",
    "* Searching for the presence of any value\n",
    "* Combining search criteria\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Text searches\n",
    "In these examples, the criteria are that the attribute *Base* in the *MaterialUniverse* table:\n",
    "\n",
    "* Contains iron (`contains=`)\n",
    "* Does not contain iron (`does_not_contain=`)\n",
    "* Contains one of three specified metals (`contains_any=`)\n",
    "* Contains all three specified metals (`contains_all=`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "base_attribute = material_universe.attributes['Base']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All materials that contain Iron:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<SearchCriterion: Base - CONTAINS - 'Fe (Iron)'>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base_contains_iron = base_attribute.search_criterion(contains='Fe (Iron)')\n",
    "base_contains_iron"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:Low alloy steel, AISI 4130, air melted, quenched & tempered>,\n",
       " <Record long name:250 maraging steel, maraged at 900F>,\n",
       " <Record long name:Low alloy steel, AISI 4130, air melted, normalized>]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "iron_records = material_universe.search_for_records_where([base_contains_iron])\n",
    "iron_records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All materials that **do not** contain Iron:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:Ceramics and glasses>,\n",
       " <Record long name:Soda barium glass>,\n",
       " <Record long name:Aluminum, 7075, wrought, T73>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, aged>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, annealed, generic>]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base_does_not_contain_iron = base_attribute.search_criterion(does_not_contain='Fe (Iron)')\n",
    "iron_free_records = material_universe.search_for_records_where([base_does_not_contain_iron])\n",
    "iron_free_records[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All materials that contain **one of** Iron, Aluminum **or** Titanium:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:250 maraging steel, maraged at 900F>,\n",
       " <Record long name:Low alloy steel, AISI 4130, air melted, normalized>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, solution treated & aged>,\n",
       " <Record long name:Aluminum, 7075, wrought, T6>,\n",
       " <Record long name:Low alloy steel, AISI 4130, air melted, quenched & tempered>,\n",
       " <Record long name:Aluminum, 7075, wrought, T73>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, aged>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, annealed, generic>]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base_contains_any_metal = base_attribute.search_criterion(contains_any=['Fe (Iron)', 'Al (Aluminum)', 'Ti (Titanium)'])\n",
    "base_any_metal_records = material_universe.search_for_records_where([base_contains_any_metal])\n",
    "base_any_metal_records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All materials that contain Iron, Aluminum **and** Titanium:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base_contains_all_metals = base_attribute.search_criterion(contains_all=['Fe (Iron)', 'Al (Aluminum)', 'Ti (Titanium)'])\n",
    "base_all_metals_records = material_universe.search_for_records_where([base_contains_all_metals])\n",
    "base_all_metals_records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Numeric searches\n",
    "In this example, the criteria are that the attribute *Yield Strength, L* in the *Design Data* table is either greater than or less than 145 ksi."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "design_data = db.get_table('Design Data', unit_system='UK Imperial', absolute_temperatures=False)\n",
    "yield_strength_l_attribute = design_data.attributes['Yield Strength, L']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Materials with a *Yield Strength* **less than** 145 ksi:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:Titanium alloys, Ti-6Al-4V>]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yield_strength_l_attribute.unit\n",
    "low_yield_strength = yield_strength_l_attribute.search_criterion(less_than=145.0)\n",
    "low_yield_strength_records = design_data.search_for_records_where([low_yield_strength])\n",
    "low_yield_strength_records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Materials with a *Yield Strength* **greater than** 145 ksi:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:250 Grade Maraging, Maraged at 900F, Plate, Thickness: 0.1875 to 0.251 in, AMS 6520, S basis>,\n",
       " <Record long name:Nickel alloys, Inconel 718, Forging>]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "high_yield_strength = yield_strength_l_attribute.search_criterion(greater_than=145.0)\n",
    "high_yield_strength_records = design_data.search_for_records_where([high_yield_strength])\n",
    "high_yield_strength_records"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Date searches\n",
    "In this example, the search is for records in the *Legislation and Lists* table with an *Effective Date* between 1970 and 2000."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "legislations = db.get_table('Legislations and Lists', unit_system='UK Imperial', absolute_temperatures=False)\n",
    "effective_date_attribute = legislations.attributes['Effective date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:TSCA Section 6>]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import datetime\n",
    "start_date = datetime.datetime(1970, 1, 1)\n",
    "end_date = datetime.datetime(2000, 1, 1)\n",
    "effective_date_1970_2000 = effective_date_attribute.search_criterion(between_dates=(start_date, end_date))\n",
    "legislations_1970_2000 = legislations.search_for_records_where([effective_date_1970_2000])\n",
    "legislations_1970_2000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Boolean searches\n",
    "Search for records with a certain Boolean value. In this example, the search is for all materials where RoHS compliant grades are available."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:Soda barium glass>,\n",
       " <Record long name:Low alloy steel, AISI 4130, air melted, quenched & tempered>,\n",
       " <Record long name:Aluminum, 7075, wrought, T73>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, aged>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, annealed, generic>]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RoHS_grades_attribute = material_universe.attributes['RoHS (EU) compliant grades?']\n",
    "\n",
    "RoHS_grades_true = RoHS_grades_attribute.search_criterion(contains=True)\n",
    "materials_with_rohs_grades = material_universe.search_for_records_where([RoHS_grades_true])\n",
    "materials_with_rohs_grades[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining criteria\n",
    "The `search_for_records_where` method takes a list of criteria, allowing criteria to be combined as long as they relate to attributes in the same table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:Low alloy steel, AISI 4130, air melted, quenched & tempered>,\n",
       " <Record long name:Aluminum, 7075, wrought, T73>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, aged>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, annealed, generic>,\n",
       " <Record long name:250 maraging steel, maraged at 900F>,\n",
       " <Record long name:Low alloy steel, AISI 4130, air melted, normalized>,\n",
       " <Record long name:Titanium, alpha-beta alloy, Ti-6Al-4V, solution treated & aged>,\n",
       " <Record long name:Aluminum, 7075, wrought, T6>]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metals_with_rohs_grades = material_universe.search_for_records_where([base_contains_any_metal, RoHS_grades_true])\n",
    "metals_with_rohs_grades"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Searching for the existence of data\n",
    "We can also define criteria that search for records which have *any* value for the specified attribute, with no restriction on the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:Dibutyl phthalate [84-74-2]>,\n",
       " <Record long name:Arsenic trioxide [1327-53-3]>,\n",
       " <Record long name:Bis (2-ethyl(hexyl)phthalate) (DEHP) [117-81-7]>,\n",
       " <Record long name:Alkanes, C10-13, chloro;  (Short chain chlorinated paraffins ) [85535-84-8]>,\n",
       " <Record long name:Potassium chromate [7789-00-6]>]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restricted_substances = db.get_table('Restricted Substances', unit_system='UK Imperial', absolute_temperatures=False)\n",
    "SVHC_criterion = restricted_substances.attributes['SVHC criterion']\n",
    "\n",
    "svhc_exists = SVHC_criterion.search_criterion(exists=True)\n",
    "svhc_materials = restricted_substances.search_for_records_where([svhc_exists])\n",
    "svhc_materials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Searching for tabular data\n",
    "Searching tabular data works in exactly the same way as searching attributes, except the criteria require an additional argument to specify the applicable column. This example finds all records in the *MaterialUniverse* table that contain at least one tabular row, and where both the following are true:\n",
    "\n",
    "* The 'Substance name' column contains the text value 'Alkanes'\n",
    "* The 'Amount' column contains a value greater than 10.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Record long name:PVC-elastomer (Shore A75, flame retarded)>,\n",
       " <Record long name:PVC-elastomer (Shore A35)>,\n",
       " <Record long name:PVC-elastomer (Shore A55)>,\n",
       " <Record long name:PVC-elastomer (Shore A75)>]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "declaration = material_universe.attributes['Restricted substances that may be associated with this material']\n",
    "\n",
    "substance_contains_alkanes = declaration.search_criterion(contains='Alkanes', in_column='Substance name')\n",
    "amount_gt_10 = declaration.search_criterion(greater_than=10.0, in_column='Amount')\n",
    "\n",
    "affected_materials = restricted_substances.search_for_records_where([substance_contains_alkanes, amount_gt_10])\n",
    "affected_materials"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Print the results of the tabular search."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                      Record Name                       |                       Short Name                       \n",
      "-----------------------------------------------------------------------------------------------------------------\n",
      "       PVC-elastomer (Shore A75, flame retarded)        |                Shore A75, flame retarded               \n",
      "               PVC-elastomer (Shore A35)                |                        Shore A35                       \n",
      "               PVC-elastomer (Shore A55)                |                        Shore A55                       \n",
      "               PVC-elastomer (Shore A75)                |                        Shore A75                       \n"
     ]
    }
   ],
   "source": [
    "print('{:^55.55} | {:^55.55}'.format('Record Name', 'Short Name'))\n",
    "print('-'*113)\n",
    "for r in affected_materials:\n",
    "    print('{:^55.55} | {:^55.55}'.format(str(r.name), r.short_name))"
   ]
  }
 ],
 "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
