{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Initial Setup" ] }, { "cell_type": "code", "execution_count": 1, "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": 8, "metadata": {}, "outputs": [], "source": [ "server = '192.168.100.64' \n", "database = 'tempBR1_copy' \n", "username = 'sa' \n", "password = '8qrVA9sbx'\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": 9, "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": 10, "metadata": {}, "outputs": [], "source": [ "chunksize = 1000\n", "method = 'multi'" ] }, { "cell_type": "code", "execution_count": 11, "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": 12, "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": [ "# Testing Codes" ] }, { "cell_type": "code", "execution_count": 13, "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", "
subject_codescript_typemachine_nobundle_nolocal_solver_idglobal_solver_idstatusexam_idno_of_erroneous_scriptlocal_start_datetimelocal_end_datetimesolving_order
cqtop_bundle_id
22058109EB010300
\n", "
" ], "text/plain": [ " subject_code script_type machine_no bundle_no local_solver_id \\\n", "cqtop_bundle_id \n", "22058 109 E B 01 \n", "\n", " global_solver_id status exam_id no_of_erroneous_script \\\n", "cqtop_bundle_id \n", "22058 0 3 0 \n", "\n", " local_start_datetime local_end_datetime solving_order \n", "cqtop_bundle_id \n", "22058 0 " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bundle_code = 'D109EB01'\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_slot_id_start\n", "bundle_df.index.name = 'cqtop_bundle_id'\n", "bundle_df" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "ename": "DBFNotFound", "evalue": "could not find file 'data/CSC/E_type/After_slv/D109EB01.PKT'", "output_type": "error", "traceback": [ "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m", "\u001b[1;31mDBFNotFound\u001b[0m Traceback (most recent call last)", "Cell \u001b[1;32mIn [14], line 2\u001b[0m\n\u001b[0;32m 1\u001b[0m slots_file \u001b[39m=\u001b[39m \u001b[39m'\u001b[39m\u001b[39mD109EB01.PKT\u001b[39m\u001b[39m'\u001b[39m\n\u001b[1;32m----> 2\u001b[0m df \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mDataFrame(DBF(\u001b[39mf\u001b[39;49m\u001b[39m'\u001b[39;49m\u001b[39m{\u001b[39;49;00mslots_path\u001b[39m}\u001b[39;49;00m\u001b[39m/\u001b[39;49m\u001b[39m{\u001b[39;49;00mslots_file\u001b[39m}\u001b[39;49;00m\u001b[39m'\u001b[39;49m))\n\u001b[0;32m 3\u001b[0m slots_df \u001b[39m=\u001b[39m pd\u001b[39m.\u001b[39mDataFrame()\n\u001b[0;32m 4\u001b[0m slots_df[\u001b[39m'\u001b[39m\u001b[39mcenter_code\u001b[39m\u001b[39m'\u001b[39m] \u001b[39m=\u001b[39m df[\u001b[39m'\u001b[39m\u001b[39mPKT_NO\u001b[39m\u001b[39m'\u001b[39m]\u001b[39m.\u001b[39mstr[\u001b[39m0\u001b[39m:\u001b[39m3\u001b[39m]\n", "File \u001b[1;32mc:\\Python34\\Lib\\site-packages\\dbfread\\dbf.py:110\u001b[0m, in \u001b[0;36mDBF.__init__\u001b[1;34m(self, filename, encoding, ignorecase, lowernames, parserclass, recfactory, load, raw, ignore_missing_memofile, char_decode_errors)\u001b[0m\n\u001b[0;32m 108\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mfilename \u001b[39m=\u001b[39m ifind(filename)\n\u001b[0;32m 109\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mfilename:\n\u001b[1;32m--> 110\u001b[0m \u001b[39mraise\u001b[39;00m DBFNotFound(\u001b[39m'\u001b[39m\u001b[39mcould not find file \u001b[39m\u001b[39m{!r}\u001b[39;00m\u001b[39m'\u001b[39m\u001b[39m.\u001b[39mformat(filename))\n\u001b[0;32m 111\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[0;32m 112\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mfilename \u001b[39m=\u001b[39m filename\n", "\u001b[1;31mDBFNotFound\u001b[0m: could not find file 'data/CSC/E_type/After_slv/D109EB01.PKT'" ] } ], "source": [ "slots_file = 'D109EB01.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'][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", "slots_df" ] }, { "cell_type": "code", "execution_count": 99, "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
00000000001100111100011011010011111011110012871541610846282109001111HSC00...0000000000000010011110001101101001111101111001
10000000002100110010111000110111110101110012871571610845340109001111HSC00...0000000000000010011001011100011011111010111001
20000000003100100001100101111001000111010012871621510243574109001111HSC00...0000000000000010010000110010111100100011101001
30000000004100111001100000101100011101010012871651510657139109001111HSC00...0000000000000010011100110000010110001110101001
40000000005100110111110110011011101100110012871661610846815109001111HSC00...0000000000000010011011111011001101110110011001
..................................................................
16510000001652000010011110101110111101010100002894001410852383109011111HSC08...0000000000000000001001111010111011110101010000
16520000001653000000001010100100001001000100002894011610855008109001111HSC08...0000000000000000000000101010010000100100010000
16530000001654000001111101110100100001101100002894021610856438109001111HSC08...0000000000000000000111110111010010000110110000
16540000001655000010100011111000001001110000002894041510663533109001111HSC08...0000000000000000001010001111100000100111000000
16550000001656000010010001010011010100110100002894051610509759109001111HSC08...0000000000000000001001000101001101010011010000
\n", "

1656 rows × 22 columns

\n", "
" ], "text/plain": [ " scan_serial litho_code roll_no \\\n", "cqtop_record_id \n", "0 0000000001 10011110001101101001111101111001 287154 \n", "1 0000000002 10011001011100011011111010111001 287157 \n", "2 0000000003 10010000110010111100100011101001 287162 \n", "3 0000000004 10011100110000010110001110101001 287165 \n", "4 0000000005 10011011111011001101110110011001 287166 \n", "... ... ... ... \n", "1651 0000001652 00001001111010111011110101010000 289400 \n", "1652 0000001653 00000000101010010000100100010000 289401 \n", "1653 0000001654 00000111110111010010000110110000 289402 \n", "1654 0000001655 00001010001111100000100111000000 289404 \n", "1655 0000001656 00001001000101001101010011010000 289405 \n", "\n", " reg_no subject_code extra_page_count alignment_marks \\\n", "cqtop_record_id \n", "0 1610846282 109 00 1111 \n", "1 1610845340 109 00 1111 \n", "2 1510243574 109 00 1111 \n", "3 1510657139 109 00 1111 \n", "4 1610846815 109 00 1111 \n", "... ... ... ... ... \n", "1651 1410852383 109 01 1111 \n", "1652 1610855008 109 00 1111 \n", "1653 1610856438 109 00 1111 \n", "1654 1510663533 109 00 1111 \n", "1655 1610509759 109 00 1111 \n", "\n", " exam_type cqtop_bundle_id cqtop_slot_id ... \\\n", "cqtop_record_id ... \n", "0 HSC 0 0 ... \n", "1 HSC 0 0 ... \n", "2 HSC 0 0 ... \n", "3 HSC 0 0 ... \n", "4 HSC 0 0 ... \n", "... ... ... ... ... \n", "1651 HSC 0 8 ... \n", "1652 HSC 0 8 ... \n", "1653 HSC 0 8 ... \n", "1654 HSC 0 8 ... \n", "1655 HSC 0 8 ... \n", "\n", " local_resolved global_resolved status is_edited image_link \\\n", "cqtop_record_id \n", "0 0 0 0 \n", "1 0 0 0 \n", "2 0 0 0 \n", "3 0 0 0 \n", "4 0 0 0 \n", "... ... ... ... ... ... \n", "1651 0 0 0 \n", "1652 0 0 0 \n", "1653 0 0 0 \n", "1654 0 0 0 \n", "1655 0 0 0 \n", "\n", " roll_autosolved subjectcode_autosolved \\\n", "cqtop_record_id \n", "0 0 0 \n", "1 0 0 \n", "2 0 0 \n", "3 0 0 \n", "4 0 0 \n", "... ... ... \n", "1651 0 0 \n", "1652 0 0 \n", "1653 0 0 \n", "1654 0 0 \n", "1655 0 0 \n", "\n", " system_updated_status responsible_for_duplicate \\\n", "cqtop_record_id \n", "0 0 00000000 \n", "1 0 00000000 \n", "2 0 00000000 \n", "3 0 00000000 \n", "4 0 00000000 \n", "... ... ... \n", "1651 0 00000000 \n", "1652 0 00000000 \n", "1653 0 00000000 \n", "1654 0 00000000 \n", "1655 0 00000000 \n", "\n", " qr_litho_code \n", "cqtop_record_id \n", "0 10011110001101101001111101111001 \n", "1 10011001011100011011111010111001 \n", "2 10010000110010111100100011101001 \n", "3 10011100110000010110001110101001 \n", "4 10011011111011001101110110011001 \n", "... ... \n", "1651 00001001111010111011110101010000 \n", "1652 00000000101010010000100100010000 \n", "1653 00000111110111010010000110110000 \n", "1654 00001010001111100000100111000000 \n", "1655 00001001000101001101010011010000 \n", "\n", "[1656 rows x 22 columns]" ] }, "execution_count": 99, "metadata": {}, "output_type": "execute_result" } ], "source": [ "records_file = 'D109EB01.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(' ', '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", "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['cqtop_slot_id'] = records_df['cqtop_slot_id'].astype(int)\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", "records_df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Final Loop" ] }, { "cell_type": "code", "execution_count": 112, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "100%|██████████| 69/69 [00:02<00:00, 31.50it/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 = 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_slot_id_start\n", " bundle_df.index.name = 'cqtop_bundle_id'\n", " # bundle_df.to_sql('CQTopBundles', engine, if_exists='append')\n", "\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']\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", " # slots_df.to_sql('CQTopSlots', engine, if_exists='append')\n", "\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(' ', '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", " 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['cqtop_slot_id'] = records_df['cqtop_slot_id'].astype(int)\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", " # 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", "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.11.0" }, "orig_nbformat": 4, "vscode": { "interpreter": { "hash": "fea6dd9932537b91dffa77fea81469d8a800d8c034f646a51a7e54bb7dc9f8ea" } } }, "nbformat": 4, "nbformat_minor": 2 }