{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f72847eb",
   "metadata": {},
   "source": [
    "# Create an Excel export job"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6639762c",
   "metadata": {},
   "source": [
    "You use an Excel export job to export data into a properly formatted Excel spreadsheet.\n",
    "\n",
    "This example shows how to create an Excel export job request, submit it to the job\n",
    "queue, and interact with the resulting Excel export job object returned by the server.\n",
    "\n",
    "Information on how to create a properly formatted Excel export template is outside the scope of\n",
    "this example. For information on the use of Excel for exporting data from Granta MI, see\n",
    "the Granta MI documentation or consult your ACE representative."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1afbf429",
   "metadata": {},
   "source": [
    "## Connect to Granta MI"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f019fff6",
   "metadata": {},
   "source": [
    "Import the ``Connection`` class and create the connection. For more information,\n",
    "see the [Connect and access the job queue](0_Getting_started.ipynb) example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "022055eb",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "from ansys.grantami.jobqueue import Connection\n",
    "\n",
    "server_url = \"http://my_grantami_server/mi_servicelayer\"\n",
    "client = Connection(server_url).with_credentials(\"user_name\", \"password\").connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf599c81",
   "metadata": {},
   "source": [
    "## Create an ``ExcelExportJobRequest`` object"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dd5d49d",
   "metadata": {},
   "source": [
    "The first step in exporting an Excel file with the job queue is to create an\n",
    "``ExcelExportJobRequest`` object. When creating this object, specify the name of the job, the\n",
    "Excel export template file, the relevant database, and the records to export. You can also\n",
    "specify an optional description and the scheduled execution date, if the export should be deferred\n",
    "until that date and time.\n",
    "\n",
    "You provide the records to export as ``ExportRecord`` objects. Records are defined in terms\n",
    "of a *record history identity* and an optional *record version*, which are used to identify\n",
    "a previous record version in version-controlled tables. To determine the record history identity,\n",
    "use of the Granta MI Python Scripting Toolkit is recommended. For more information, consult your\n",
    "ACE representative.\n",
    "\n",
    "You can provide the Excel export template file as a relative or absolute path, or as a\n",
    "``pathlib.Path`` objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17ef670a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from ansys.grantami.jobqueue import ExcelExportJobRequest, ExportRecord\n",
    "\n",
    "export_records = [\n",
    "    ExportRecord(record_history_identity=120732),\n",
    "    ExportRecord(record_history_identity=120733),\n",
    "]\n",
    "excel_export_request = ExcelExportJobRequest(\n",
    "    name=\"Excel Export\",\n",
    "    description=\"An example excel export job\",\n",
    "    template_file=\"assets/export_template.xlsx\",\n",
    "    database_key=\"MI_Training\",\n",
    "    records=export_records,\n",
    ")\n",
    "excel_export_request"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b4b22e4",
   "metadata": {},
   "source": [
    "## Submit the job to the server\n",
    "Next, submit the jobs to the server. There are two methods for submitting job\n",
    "requests:\n",
    "\n",
    "* ``create_job()``: Submit the job request to the server and immediately return an\n",
    "  ``AsyncJob`` object in the *pending* state.\n",
    "* ``create_job_and_wait()``: Submit the job request to the server and block until the job\n",
    "   either completes or fails. Return an ``AsyncJob`` object in the *succeeded* or *failed* state.\n",
    "\n",
    "This example uses the ``create_job_and_wait()`` method. For an example that shows\n",
    "how to create and submit a job that runs asynchronously, see\n",
    "[Schedule and modify jobs](4_Scheduling_and_modifying_jobs.ipynb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1016450e",
   "metadata": {},
   "outputs": [],
   "source": [
    "completed_job = client.create_job_and_wait(excel_export_request)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2a157f6",
   "metadata": {},
   "source": [
    "## Access output files\n",
    "Finally, access the results of the job. Export jobs typically create log files and either a\n",
    "single Excel output file or a ZIP file containing multiple Excel files and/or attachments."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0383a9d9",
   "metadata": {},
   "source": [
    "Access the list of files generated by the job with the ``output_file_names`` property. This\n",
    "returns a list of file names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66f01135",
   "metadata": {},
   "outputs": [],
   "source": [
    "completed_job.output_file_names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16aa7d1b",
   "metadata": {},
   "source": [
    "In general, an Excel export job returns three files:\n",
    "\n",
    "- ``<job name>.log``: Log file of the import operation on the server\n",
    "- ``summary.json``: Data file that summarizes the number of records exported by the job and\n",
    "  provides details of any errors that occurred during processing.\n",
    "- A file containing the data exported from Granta MI\n",
    "\n",
    "The contents of the exported data file depends on the job:\n",
    "\n",
    "* If one Excel file is generated and no attachments are included in the export,\n",
    "  the exported file is the Excel file generated by the export operation.\n",
    "* If there are multiple Excel files generated, or if attachments are included in the export,\n",
    "  the exported file is a ZIP file of all the exported files."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c491ec2",
   "metadata": {},
   "source": [
    "This cell shows how to access the content of the log file as ``bytes`` using the\n",
    "``AsyncJob.get_file_content()`` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47e28015",
   "metadata": {},
   "outputs": [],
   "source": [
    "log_file_name = next(name for name in completed_job.output_file_names if \"log\" in name)\n",
    "log_file_content = completed_job.get_file_content(log_file_name)\n",
    "log_file_string = log_file_content.decode(\"utf-8\")\n",
    "print(f\"{log_file_name} (first 200 characters):\")\n",
    "print(f\"{log_file_string[:500]}...\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb26f4dd",
   "metadata": {},
   "source": [
    "This next cell shows how to download the Excel file and export summary file to disk using the\n",
    "``AsyncJob.download_file()`` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fdd18b50",
   "metadata": {},
   "outputs": [],
   "source": [
    "output_file_name = next(name for name in completed_job.output_file_names if name.endswith(\"xlsx\"))\n",
    "output_path = f\"./{output_file_name}\"\n",
    "completed_job.download_file(output_file_name, output_path)\n",
    "print(f\"{output_file_name} saved to disk\")\n",
    "\n",
    "summary_file_name = next(name for name in completed_job.output_file_names if name == \"summary.json\")\n",
    "output_path = f\"./{summary_file_name}\"\n",
    "completed_job.download_file(summary_file_name, output_path)\n",
    "print(f\"{summary_file_name} saved to disk\")"
   ]
  }
 ],
 "metadata": {
  "granta": {
   "clean_database": true
  },
  "jupytext": {
   "notebook_metadata_filter": "granta"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}