Task_Creataion_Logic.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem Statement - \n",
    "Increase in customer chat pendency due to high volume  \n",
    "### Code Functionality- \n",
    "Take input for the pending chats for customers, depending on the channel the customer was redirected from, check customer selection, cuurent status for the order and raise appropriate tasks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Input the file containing raised issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql.cursors\n",
    "import pandas as pd\n",
    "\n",
    "# Mention file name and path for Line ID mapped file\n",
    "incidetails=pd.read_excel(\"File.xlsx\")\n",
    "\n",
    "packets = incidetails[(incidetails['Leg']=='Forward')&(~incidetails['lineId'].isnull())]['lineId']\n",
    "packets = packets.dropna(how = 'all')\n",
    "p = ','.join(['\\''+str(int(i))+'\\'' for i in list(packets.unique())])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract further details and check the current status for the issues"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\300068032\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\pymysql\\cursors.py:329: Warning: (1292, \"Truncated incorrect DOUBLE value: 's'\")\n",
      "  self._do_get_result()\n"
     ]
    }
   ],
   "source": [
    "import pymysql.cursors\n",
    "\n",
    "# Extract details for the line ids\n",
    "Forward_details_lineid=\"\"\"\n",
    "select\n",
    "'s'+ol.store_order_id as store_order_id,\n",
    "ol.id as entity,\n",
    "ol.packet_id_fk,\n",
    "ol.courier_code,\n",
    "ol.packed_on,\n",
    "ol.shipped_on,\n",
    "cast(ol.created_on as date) as created_on,\n",
    "ol.cancelled_on,\n",
    "ol.status_code,\n",
    "zipcode,\n",
    "max(APD) as APD,\n",
    "max(EPD) as EPD,\n",
    "max(ESD) as ESD,\n",
    "max(ol.delivered_on) as delivered_date\n",
    "from order_line ol \n",
    "left join (select \n",
    "cast((case when orai.key in ('Actual_PROMISE_TIME') then value end) as date) as APD,\n",
    "cast((case when orai.key in ('EXPECTED_PACKING_TIME') then value end) as date) as EPD,\n",
    "cast((case when orai.key in ('EXPECTED_CUTOFF_TIME') then value end) as date) as ESD,\n",
    "order_release_id_fk\n",
    "from  order_release_additional_info orai\n",
    "where order_release_id_fk in (select order_release_id_fk from order_line where id in ( \"\"\"+p+\"\"\")\n",
    "and orai.key in ('Actual_PROMISE_TIME','EXPECTED_PACKING_TIME','EXPECTED_CUTOFF_TIME')\n",
    ")\n",
    ") orai\n",
    "on ol.order_release_id_fk=orai.order_release_id_fk\n",
    "left join order_release_VIEW ord\n",
    "on ol.store_order_id=ord.store_order_id\n",
    "where ol.id in ( \"\"\"+p+\"\"\") \n",
    "group by 1,2,3,4,5,6,7,8,9\n",
    "\"\"\"\n",
    "\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='pswrd',                             \n",
    "                            db='db',\n",
    "                            charset='charset',\n",
    "                            cursorclass=pymysql.cursors.DictCursor)\n",
    "Forward_details_lineid_df=pd.read_sql(Forward_details_lineid,connection)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {},
   "outputs": [],
   "source": [
    "packets = Forward_details_lineid_df.packet_id_fk\n",
    "packets = packets.dropna(how = 'all')\n",
    "p = ','.join(['\\''+str(int(i))+'\\'' for i in list(packets.unique())])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract failed attempts and loaction information for the line ids\n",
    "Forward_location=\"\"\"\n",
    "select ots.order_id, som.status,zipcode,\n",
    "max(failed_attempts) as failed_attempts,\n",
    "max(dc_inscanned_date) as dc_inscanned_date\n",
    "from order_to_ship ots\n",
    "left join \n",
    "\t(\n",
    "\t\tselect order_id, status,\n",
    "\t\tmax(last_modified_on) as last_modified_on\n",
    "\t\tfrom shipment_order_map \n",
    "\t\twhere order_id in (\"\"\"+p+\"\"\")\n",
    "\t\tgroup by 1\n",
    "\t) som\n",
    "on ots.order_id=som.order_id\n",
    "left join \n",
    "\t(\n",
    "\t\tselect order_id, failed_attempts\n",
    "\t\tfrom order_tracking ot\n",
    "\t\twhere order_id in (\"\"\"+p+\"\"\")\n",
    "\t)ot\n",
    "on ots.order_id=ot.order_id\n",
    "left join \n",
    "\t(\n",
    "\t\tselect source_reference_id , max(received_date) as dc_inscanned_date\n",
    "\t\tfrom ml_shipment mls\n",
    "\t\twhere source_reference_id in (\"\"\"+p+\"\"\")\n",
    "        group by 1\n",
    "\t)mls\n",
    "on ots.order_id=mls.source_reference_id \n",
    "where ots.order_id in (\"\"\"+p+\"\"\")\n",
    "group by 1,2,3\n",
    "\"\"\"\n",
    "\n",
    "connection = pymysql.connect(host='host',\n",
    "                            user='user',\n",
    "                            password='prwrd,                             \n",
    "                            db='db',\n",
    "                            charset='charset',\n",
    "                            cursorclass=pymysql.cursors.DictCursor)\n",
    "Forward_location_df=pd.read_sql(Forward_location,connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "Forward_location_df['order_id']=Forward_location_df['order_id'].fillna(0).astype(np.int64).astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "Forward_details_lineid_df['packet_id_fk']=Forward_details_lineid_df['packet_id_fk'].fillna(0).astype(np.int64).astype(str)\n",
    "Forward_concat_1=pd.merge(Forward_details_lineid_df,Forward_location_df, how='left', left_on='packet_id_fk', right_on='order_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date \n",
    "\n",
    "Forward_concat_1['EPD'] = pd.to_datetime(Forward_concat_1['EPD'], format='%Y-%m-%d')\n",
    "Forward_concat_1['APD'] = pd.to_datetime(Forward_concat_1['APD'], format='%Y-%m-%d')\n",
    "Forward_concat_1['ESD'] = pd.to_datetime(Forward_concat_1['ESD'], format='%Y-%m-%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [],
   "source": [
    "Forward_concat_1['failed_attempts']=Forward_concat_1['failed_attempts'].fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Logi for Task generation basis individual cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\300068032\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:8: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n",
      "  \n",
      "C:\\Users\\300068032\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:17: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n",
      "C:\\Users\\300068032\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:25: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n",
      "C:\\Users\\300068032\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:42: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n",
      "C:\\Users\\300068032\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:49: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n",
      "C:\\Users\\300068032\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:58: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n",
      "C:\\Users\\300068032\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:67: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n",
      "C:\\Users\\300068032\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:75: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n"
     ]
    }
   ],
   "source": [
    "# Task Generation Logic\n",
    "Forward_concat_1.loc[((Forward_concat_1['status_code']=='S')\n",
    "                    &(Forward_concat_1['failed_attempts']<1)\n",
    "                    &(Forward_concat_1['courier_code']=='ML')\n",
    "                    &(date.today()<=Forward_concat_1['APD'])\n",
    "\t\t\t)\n",
    "                ,'Task_Status'\n",
    "    \n",
    "] = 'Customer_Awareness_Anxiety'\n",
    "\n",
    "Forward_concat_1.loc[((Forward_concat_1['status_code'].isin(['WP','Q','RFR']))\n",
    "                    \n",
    "                    &(Forward_concat_1['failed_attempts']<1)\n",
    "                    &(date.today()<=Forward_concat_1['EPD'])\n",
    "\t\t\t)\n",
    "                ,'Task_Status'\n",
    "    \n",
    "] = 'Customer_Awareness_Anxiety'\n",
    "\n",
    "Forward_concat_1.loc[((Forward_concat_1['status_code']=='PK')\n",
    "                     &(Forward_concat_1['failed_attempts']<1)\n",
    "                    &(date.today()<=Forward_concat_1['ESD'])\n",
    "\t\t\t)\n",
    "                ,'Task_Status'\n",
    "    \n",
    "] = 'Customer_Awareness_Anxiety'\n",
    "\n",
    "\n",
    "Forward_concat_1.loc[((Forward_concat_1['status_code'].isin(['WP','Q','RFR']))\n",
    "                    &(date.today()>Forward_concat_1['EPD'])\n",
    "\t\t\t)\n",
    "                ,'Task_Status'\n",
    "    \n",
    "] = 'Delay_In_Packing'\n",
    "\n",
    "Forward_concat_1.loc[((Forward_concat_1['status_code']=='PK')\n",
    "                    &(date.today()>Forward_concat_1['ESD'])\n",
    "\t\t\t)\n",
    "                ,'Task_Status'\n",
    "    \n",
    "] = 'Delay_In_Shipping'\n",
    "\n",
    "Forward_concat_1.loc[((Forward_concat_1['status_code']=='S')\n",
    "                    &(date.today()>Forward_concat_1['APD'])\n",
    "                    &(Forward_concat_1['dc_inscanned_date'].isnull())\n",
    "                    &(Forward_concat_1['courier_code']=='ML')\n",
    "\t\t\t)\n",
    "                ,'Task_Status'\n",
    "    \n",
    "] = 'Delayed_delivery_Not_reached_DC_ML'\n",
    "\n",
    "Forward_concat_1.loc[((Forward_concat_1['status_code']=='S')\n",
    "                    &(date.today()>Forward_concat_1['APD'])\n",
    "                    &(~Forward_concat_1['dc_inscanned_date'].isnull())\n",
    "                    &(Forward_concat_1['courier_code']=='ML')\n",
    "\t\t\t)\n",
    "                ,'Task_Status'\n",
    "    \n",
    "] = 'Delayed_delivery_reached_DC_ML'\n",
    "\n",
    "Forward_concat_1.loc[((Forward_concat_1['status_code']=='S')\n",
    "                    &(date.today()>Forward_concat_1['APD'])\n",
    "                    &(Forward_concat_1['courier_code']!='ML')\n",
    "\t\t\t)\n",
    "                ,'Task_Status'\n",
    "    \n",
    "] = 'Delayed_delivery_3PL'\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [],
   "source": [
    "incidetails['lineId']=incidetails['lineId'].fillna(0).astype(np.int64).astype(str)\n",
    "Forward_concat_1['entity']=Forward_concat_1['entity'].fillna(0).astype(np.int64).astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [],
   "source": [
    "Forward_details_1=pd.merge(incidetails,Forward_concat_1, how='left', left_on='lineId', right_on='entity')\n",
    "\n",
    "Forward_details_1.to_csv('File 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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}