{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 高考志愿管理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2020年高考录取信息导入MongoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pymongo\n",
    "import decimal\n",
    "\n",
    "myclient = pymongo.MongoClient('mongodb://localhost:27017/')\n",
    "mydb = myclient[\"gaokao\"]\n",
    "mycol = mydb[\"college\"]\n",
    "mycol1 = mydb[\"admission_2020\"]\n",
    "\n",
    "m_col = {}\n",
    "m_spe = {}\n",
    "m_xx = {}\n",
    "for x in mycol.find({\"code\":{'$exists': 'true'}},{\"_id\": 0, \"code\": 1, \"name\": 1}):\n",
    "    m_col[x['code']] = x['name']\n",
    "\n",
    "\n",
    "db = pymysql.connect(host = \"localhost\",user = \"songyi\",password = \"yylzs\",database = \"gaokao\" )\n",
    "cursor = db.cursor()\n",
    "sql = 'SELECT a.code,a.college,a.name FROM speciality AS a WHERE a.nian=\"2020\"'\n",
    "cursor.execute(sql)\n",
    "results = cursor.fetchall()\n",
    "for result in results:\n",
    "    m_spe.setdefault(result[1],{})    \n",
    "    m_spe[result[1]][result[0]] = result[2]\n",
    "sql = 'SELECT * FROM admission_2020 AS a ORDER BY a.rank_min'\n",
    "cursor.execute(sql)\n",
    "results = cursor.fetchall()\n",
    "i = 0\n",
    "m_min = 0\n",
    "ii = 0\n",
    "for result in results:\n",
    "    m_xx.clear()\n",
    "    if result[6] == m_min:\n",
    "        ii = ii\n",
    "        i = i+1\n",
    "    else:\n",
    "        i = i+1\n",
    "        ii = i\n",
    "        m_min = result[6]\n",
    "    m_xx['pos'] = ii\n",
    "    m_xx['col_code'] = result[1]\n",
    "    m_xx['col_name'] = m_col[result[1]]\n",
    "    m_xx['spe_code'] = result[2]\n",
    "    m_xx['spe_name'] = m_spe[result[1]][result[2]]\n",
    "    m_xx['plan'] = result[3]\n",
    "    m_xx['dispense'] = result[5]\n",
    "    m_xx['num_min'] = result[6]\n",
    "    m_xx['num_avg'] = int(result[7])\n",
    "    m_xx['rank_min'] = result[8]\n",
    "    m_xx['nian'] = '2020'    \n",
    "    mycol1.insert_one(m_xx)\n",
    "#print(m_spe)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 计算志愿分数概率"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "633 619\n",
      "634 556\n",
      "635 450\n",
      "636 397\n",
      "637 328\n",
      "638 275\n",
      "639 351\n"
     ]
    }
   ],
   "source": [
    "import random\n",
    "\n",
    "array2 = []\n",
    "for i in range(10000):\n",
    "    array1 = []\n",
    "    s = 0\n",
    "    for ii in range(12):\n",
    "        m1 = random.randint(633,639)\n",
    "        array1.append(m1)\n",
    "        s = s + m1    \n",
    "    m_avg = round(s/12,2)\n",
    "    if m_avg == 635.5 and (633 in array1) and (639 in array1):\n",
    "        #print(array1)\n",
    "        array2.extend(array1)\n",
    "        \n",
    "#print(array2)\n",
    "m_set = set(array2)\n",
    "for m in m_set:\n",
    "    print(m,array2.count(m))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pymongo.results.InsertOneResult at 0x7fbebaa57e80>"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import openpyxl\n",
    "import pymongo\n",
    "\n",
    "myclient = pymongo.MongoClient('mongodb://localhost:27017/')\n",
    "mydb = myclient[\"gaokao\"]\n",
    "mycol = mydb[\"admission_college\"]\n",
    "wb = openpyxl.load_workbook('./data/shandongdaxue.xlsx')\n",
    "sheet = wb.active\n",
    "#sheets = wb.sheetnames\n",
    "code = 'A422'\n",
    "name = '山东大学'\n",
    "new_col = []\n",
    "dict1 = {}\n",
    "\n",
    "new_code = []\n",
    "dict1['code'] = code\n",
    "dict1['name'] = name\n",
    "for n in range(1,sheet.max_row+1):\n",
    "    nian = str(sheet.cell(n,1).value)\n",
    "    dict2 = {}\n",
    "    \n",
    "    dict1.setdefault(nian,[])\n",
    "    if sheet.cell(n,2).value =='理工':\n",
    "        m_lb = 'l'\n",
    "    elif sheet.cell(n,2).value =='文史':\n",
    "        m_lb = 'w'\n",
    "    else:\n",
    "        m_lb = 'z'    \n",
    "    dict2['type'] = m_lb\n",
    "    dict2['spe_name'] = sheet.cell(n,4).value\n",
    "    dict2['max_score'] = sheet.cell(n,5).value\n",
    "    dict2['min_score'] = sheet.cell(n,6).value\n",
    "    dict2['avg_score'] = sheet.cell(n,7).value\n",
    "    dict2['dispense'] = sheet.cell(n,8).value\n",
    "    dict1[nian].append(dict2)\n",
    "mycol.insert_one(dict1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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": 4
}