{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Initial Setup" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "import os\n", "import numpy as np\n", "import pandas as pd\n", "from dbfread import DBF\n", "from sqlalchemy import create_engine\n", "from tqdm import tqdm" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [], "source": [ "server = '43.224.110.84' \n", "database = 'tempBR' \n", "username = 'sa' \n", "password = '8qrVA9sb'\n", "\n", "engine = create_engine(\n", " f'mssql+pyodbc://{username}:{password}@{server}/{database}?driver=ODBC+Driver+17+for+SQL+Server'\n", ")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Check these before proceeding" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [], "source": [ "exam_id = 3 # take from db\n", "exam_type = 'HSC'\n", "\n", "slots_path = 'data/CSC/E_type/After_slv'\n", "records_path = 'data/CSC/E_type/Raw_dat'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Database write setup" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [], "source": [ "chunksize = 1000\n", "method = 'multi'" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "centers_df = pd.read_sql(\n", " \"\\\n", " SELECT center_code, center_id\\\n", " FROM Centers\\\n", " \",\n", " engine,\n", " index_col='center_code'\n", ")" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [], "source": [ "cqtop_bundle_id_start = pd.read_sql(\n", " \"\\\n", " SELECT max(cqtop_bundle_id) + 1\\\n", " FROM CQTopBundles\\\n", " \",\n", " engine\n", ").iloc[0,0]\n", "\n", "cqtop_slot_id_start = pd.read_sql(\n", " \"\\\n", " SELECT max(cqtop_slot_id) + 1\\\n", " FROM CQTopSlots\\\n", " \",\n", " engine\n", ").iloc[0,0]\n", "\n", "cqtop_record_id_start = pd.read_sql(\n", " \"\\\n", " SELECT max(cqtop_record_id) + 1\\\n", " FROM CQTopRecords\\\n", " \",\n", " engine\n", ").iloc[0,0]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Process Single File" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
subject_codemachine_nobundle_nolocal_solver_idglobal_solver_idstatusexam_idno_of_erroneous_scriptlocal_start_datetimelocal_end_datetimesolving_order
cqtop_bundle_id
194109B010300
\n", "
" ], "text/plain": [ " subject_code machine_no bundle_no local_solver_id \\\n", "cqtop_bundle_id \n", "194 109 B 01 \n", "\n", " global_solver_id status exam_id no_of_erroneous_script \\\n", "cqtop_bundle_id \n", "194 0 3 0 \n", "\n", " local_start_datetime local_end_datetime solving_order \n", "cqtop_bundle_id \n", "194 0 " ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def get_bundle(bundle_code, cqtop_bundle_id_start):\n", " bundle_df = pd.DataFrame({}, index=[0])\n", " bundle_df['subject_code'] = bundle_code[1:4]\n", " # bundle_df['script_type'] = bundle_code[4:5]\n", " bundle_df['machine_no'] = bundle_code[5:6]\n", " bundle_df['bundle_no'] = bundle_code[6:8]\n", " bundle_df['local_solver_id'] = ''\n", " bundle_df['global_solver_id'] = ''\n", " bundle_df['status'] = 0\n", " bundle_df['exam_id'] = exam_id\n", " bundle_df['no_of_erroneous_script'] = 0\n", " bundle_df['local_start_datetime'] = ''\n", " bundle_df['local_end_datetime'] = ''\n", " bundle_df['solving_order'] = 0\n", " bundle_df.index += cqtop_bundle_id_start\n", " bundle_df.index.name = 'cqtop_bundle_id'\n", " return bundle_df\n", "\n", "bundle_code = 'D109EB01'\n", "bundle_df = get_bundle(bundle_code, cqtop_bundle_id_start)\n", "bundle_df" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
center_idslot_nosubject_codecqtop_bundle_idtotal_scriptstart_rollend_rollactual_tsno_of_rejectedstatuserror_string
cqtop_slot_id
197060011091942002871912000
197161021091941492883471490
1972121031091941973197041970
1973118041091941573183141570
1974121051091942003192472000
1975116061091942003151542000
1976122071091942003185062000
197772081091941532948671530
197870091091942002894052000
\n", "
" ], "text/plain": [ " center_id slot_no subject_code cqtop_bundle_id total_script \\\n", "cqtop_slot_id \n", "1970 60 01 109 194 200 \n", "1971 61 02 109 194 149 \n", "1972 121 03 109 194 197 \n", "1973 118 04 109 194 157 \n", "1974 121 05 109 194 200 \n", "1975 116 06 109 194 200 \n", "1976 122 07 109 194 200 \n", "1977 72 08 109 194 153 \n", "1978 70 09 109 194 200 \n", "\n", " start_roll end_roll actual_ts no_of_rejected status \\\n", "cqtop_slot_id \n", "1970 287191 200 0 \n", "1971 288347 149 0 \n", "1972 319704 197 0 \n", "1973 318314 157 0 \n", "1974 319247 200 0 \n", "1975 315154 200 0 \n", "1976 318506 200 0 \n", "1977 294867 153 0 \n", "1978 289405 200 0 \n", "\n", " error_string \n", "cqtop_slot_id \n", "1970 \n", "1971 \n", "1972 \n", "1973 \n", "1974 \n", "1975 \n", "1976 \n", "1977 \n", "1978 " ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def get_slots(slots_path, bundle_code, bundle_df, cqtop_slot_id_start):\n", " slots_file = f'{bundle_code}.PKT'\n", " df = pd.DataFrame(DBF(f'{slots_path}/{slots_file}'))\n", " slots_df = pd.DataFrame()\n", " slots_df['center_code'] = df['PKT_NO'].str[0:3]\n", " slots_df = slots_df.join(centers_df, on='center_code')[['center_id']]\n", " slots_df['slot_no'] = df['PKT_NO'].str[3:5]\n", " slots_df['subject_code'] = bundle_df['subject_code'].iloc[0]\n", " slots_df['cqtop_bundle_id'] = bundle_df.index[0]\n", " slots_df['total_script'] = df['TS_TOTAL']\n", " slots_df['start_roll'] = ''\n", " slots_df['end_roll'] = df['ENDROLL']\n", " slots_df['actual_ts'] = df['SCANNED']\n", " slots_df['no_of_rejected'] = df['MARK_REJ']\n", " slots_df['status'] = df['STATUS']\n", " slots_df['error_string'] = ''\n", " slots_df.index += cqtop_slot_id_start\n", " slots_df.index.name = 'cqtop_slot_id'\n", " return slots_df\n", "\n", "slots_df = get_slots(slots_path, bundle_code, bundle_df, cqtop_slot_id_start)\n", "slots_df" ] }, { "cell_type": "code", "execution_count": 24, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
scan_seriallitho_coderoll_noreg_nosubject_codeextra_page_countalignment_marksexam_typecqtop_bundle_idcqtop_slot_id...local_resolvedglobal_resolvedstatusis_editedimage_linkroll_autosolvedsubjectcode_autosolvedsystem_updated_statusresponsible_for_duplicateqr_litho_code
cqtop_record_id
2788010000000001100111100011011010011111011110012871541610846282109NaN1111HSC1941970...0000000000000010011110001101101001111101111001
2788020000000002100110010111000110111110101110012871571610845340109NaN1111HSC1941970...0000000000000010011001011100011011111010111001
2788030000000003100100001100101111001000111010012871621510243574109NaN1111HSC1941970...0000000000000010010000110010111100100011101001
2788040000000004100111001100000101100011101010012871651510657139109NaN1111HSC1941970...0000000000000010011100110000010110001110101001
2788050000000005100110111110110011011101100110012871661610846815109NaN1111HSC1941970...0000000000000010011011111011001101110110011001
..................................................................
2804520000001652000010011110101110111101010100002894001410852383109011111HSC1941978...0000000000000000001001111010111011110101010000
2804530000001653000000001010100100001001000100002894011610855008109NaN1111HSC1941978...0000000000000000000000101010010000100100010000
2804540000001654000001111101110100100001101100002894021610856438109NaN1111HSC1941978...0000000000000000000111110111010010000110110000
2804550000001655000010100011111000001001110000002894041510663533109NaN1111HSC1941978...0000000000000000001010001111100000100111000000
2804560000001656000010010001010011010100110100002894051610509759109NaN1111HSC1941978...0000000000000000001001000101001101010011010000
\n", "

1656 rows × 22 columns

\n", "
" ], "text/plain": [ " scan_serial litho_code roll_no \\\n", "cqtop_record_id \n", "278801 0000000001 10011110001101101001111101111001 287154 \n", "278802 0000000002 10011001011100011011111010111001 287157 \n", "278803 0000000003 10010000110010111100100011101001 287162 \n", "278804 0000000004 10011100110000010110001110101001 287165 \n", "278805 0000000005 10011011111011001101110110011001 287166 \n", "... ... ... ... \n", "280452 0000001652 00001001111010111011110101010000 289400 \n", "280453 0000001653 00000000101010010000100100010000 289401 \n", "280454 0000001654 00000111110111010010000110110000 289402 \n", "280455 0000001655 00001010001111100000100111000000 289404 \n", "280456 0000001656 00001001000101001101010011010000 289405 \n", "\n", " reg_no subject_code extra_page_count alignment_marks \\\n", "cqtop_record_id \n", "278801 1610846282 109 NaN 1111 \n", "278802 1610845340 109 NaN 1111 \n", "278803 1510243574 109 NaN 1111 \n", "278804 1510657139 109 NaN 1111 \n", "278805 1610846815 109 NaN 1111 \n", "... ... ... ... ... \n", "280452 1410852383 109 01 1111 \n", "280453 1610855008 109 NaN 1111 \n", "280454 1610856438 109 NaN 1111 \n", "280455 1510663533 109 NaN 1111 \n", "280456 1610509759 109 NaN 1111 \n", "\n", " exam_type cqtop_bundle_id cqtop_slot_id ... \\\n", "cqtop_record_id ... \n", "278801 HSC 194 1970 ... \n", "278802 HSC 194 1970 ... \n", "278803 HSC 194 1970 ... \n", "278804 HSC 194 1970 ... \n", "278805 HSC 194 1970 ... \n", "... ... ... ... ... \n", "280452 HSC 194 1978 ... \n", "280453 HSC 194 1978 ... \n", "280454 HSC 194 1978 ... \n", "280455 HSC 194 1978 ... \n", "280456 HSC 194 1978 ... \n", "\n", " local_resolved global_resolved status is_edited image_link \\\n", "cqtop_record_id \n", "278801 0 0 0 \n", "278802 0 0 0 \n", "278803 0 0 0 \n", "278804 0 0 0 \n", "278805 0 0 0 \n", "... ... ... ... ... ... \n", "280452 0 0 0 \n", "280453 0 0 0 \n", "280454 0 0 0 \n", "280455 0 0 0 \n", "280456 0 0 0 \n", "\n", " roll_autosolved subjectcode_autosolved \\\n", "cqtop_record_id \n", "278801 0 0 \n", "278802 0 0 \n", "278803 0 0 \n", "278804 0 0 \n", "278805 0 0 \n", "... ... ... \n", "280452 0 0 \n", "280453 0 0 \n", "280454 0 0 \n", "280455 0 0 \n", "280456 0 0 \n", "\n", " system_updated_status responsible_for_duplicate \\\n", "cqtop_record_id \n", "278801 0 00000000 \n", "278802 0 00000000 \n", "278803 0 00000000 \n", "278804 0 00000000 \n", "278805 0 00000000 \n", "... ... ... \n", "280452 0 00000000 \n", "280453 0 00000000 \n", "280454 0 00000000 \n", "280455 0 00000000 \n", "280456 0 00000000 \n", "\n", " qr_litho_code \n", "cqtop_record_id \n", "278801 10011110001101101001111101111001 \n", "278802 10011001011100011011111010111001 \n", "278803 10010000110010111100100011101001 \n", "278804 10011100110000010110001110101001 \n", "278805 10011011111011001101110110011001 \n", "... ... \n", "280452 00001001111010111011110101010000 \n", "280453 00000000101010010000100100010000 \n", "280454 00000111110111010010000110110000 \n", "280455 00001010001111100000100111000000 \n", "280456 00001001000101001101010011010000 \n", "\n", "[1656 rows x 22 columns]" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def get_records(records_path, bundle_code, bundle_df, slots_df, cqtop_slot_id_start):\n", " records_file = f'{bundle_code}.DAT'\n", " records_df = pd.read_fwf(f'{records_path}/{records_file}', colspecs=[(0,10), (41,73), (73,79), (80,90), (91,94), (97,99), (99,103)], header=None, dtype=str, delimiter=\"\\n\\t\").replace(r'^\\s*$', np.nan, regex=True).replace(' ', '0', regex=True)\n", " records_df.columns = ['scan_serial','litho_code','roll_no','reg_no','subject_code','extra_page_count','alignment_marks']\n", " records_df['exam_type'] = exam_type\n", " records_df['cqtop_bundle_id'] = bundle_df.index[0]\n", " records_df['cqtop_slot_id'] = 0\n", " for i, n, stop in zip(slots_df.index, slots_df['actual_ts'], slots_df['actual_ts'].cumsum()):\n", " records_df.loc[stop-n:stop, 'cqtop_slot_id'] = i\n", " records_df['script_serial'] = range(len(records_df))\n", " records_df['error_string'] = '00000000000000000000000'\n", " records_df['local_resolved'] = 0\n", " records_df['global_resolved'] = 0\n", " records_df['status'] = ''\n", " records_df['is_edited'] = 0\n", " records_df['image_link'] = ''\n", " records_df['roll_autosolved'] = 0\n", " records_df['subjectcode_autosolved'] = 0\n", " records_df['system_updated_status'] = 0\n", " records_df['responsible_for_duplicate'] = '00000000'\n", " records_df['qr_litho_code'] = records_df['litho_code']\n", " records_df.index += cqtop_record_id_start\n", " records_df.index.name = 'cqtop_record_id'\n", " return records_df\n", "\n", "records_df = get_records(records_path, bundle_code, bundle_df, slots_df, cqtop_record_id_start)\n", "records_df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Process All Files" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "100%|██████████| 69/69 [00:02<00:00, 23.79it/s]\n" ] } ], "source": [ "bundle_codes = set(f[:-4] for f in os.listdir(slots_path) if f.endswith('.PKT')) & set(f[:-4] for f in os.listdir(records_path) if f.endswith('.DAT'))\n", "\n", "for bundle_code in tqdm(bundle_codes):\n", " bundle_df = get_bundle(bundle_code, cqtop_bundle_id_start)\n", " # bundle_df.to_sql('CQTopBundles', engine, if_exists='append')\n", "\n", " slots_df = get_slots(slots_path, bundle_code, bundle_df, cqtop_slot_id_start)\n", " # slots_df.to_sql('CQTopSlots', engine, if_exists='append')\n", "\n", " records_df = get_records(records_path, bundle_code, bundle_df, slots_df, cqtop_record_id_start)\n", " # records_df.to_sql('CQTopRecords', engine, if_exists='append', chunksize=chunksize, method=method)\n", "\n", " cqtop_bundle_id_start += len(bundle_df)\n", " cqtop_slot_id_start += len(slots_df)\n", " cqtop_record_id_start += len(records_df)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.10.6 ('omrproc')", "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.6" }, "orig_nbformat": 4, "vscode": { "interpreter": { "hash": "407bfa29f0752efef9195014b440e6d1aba1f35e71977bc4a28836510e310ec6" } } }, "nbformat": 4, "nbformat_minor": 2 }