#-*- coding:utf-8 -*-
import sqlite3
#打開本地數(shù)據(jù)庫用于存儲用戶信息
cx = sqlite3.connect('student.db')

#在該數(shù)據(jù)庫下創(chuàng)建學(xué)生信息表
cx.execute ('''CREATE TABLE StudentTable(
 ID INTEGER PRIMARY KEY AUTOINCREMENT,
 StuId   INTEGER  NOT NULL,
 NAME   TEXT  NOT NULL,
 CLASS   INT  NOT NULL);''')
print "Table created successfully";

#在該數(shù)據(jù)庫下創(chuàng)建課程信息表
cx.execute ('''CREATE TABLE CourseTable(
 ID INTEGER PRIMARY KEY AUTOINCREMENT,
 CourseId  INT  NOT NULL,
 Name   TEXT  NOT NULL,
 Teacher   TEXT  NOT NULL,
 Classroom   TEXT  NOT NULL,
 StartTime    CHAR(11) NOT NULL,
 EndTime    CHAR(11) NOT NULL);''')
print "Table created successfully";


#在該數(shù)據(jù)庫下創(chuàng)建選課情況信息表
cx.execute ('''CREATE TABLE XuankeTable(
 ID INTEGER PRIMARY KEY AUTOINCREMENT,
 StuId   INT   NOT NULL,
 CourseId   INT   NOT NULL);''')
print "Table created successfully";

#以上三個表創(chuàng)建完后，再次運(yùn)行程序時，需要把三個建表代碼注釋掉，否則會提示:該表已存在。即建表只需建一次。


def insert_stu():#錄入學(xué)生信息
 cu = cx.cursor()
 stu_id = input("請輸入學(xué)生學(xué)號:")
 cu.execute("SELECT StuId from StudentTable where StuId = '%s';"%stu_id)
 row = cu.fetchone()
 if row:
  print "sorry,該學(xué)號已存在,請重新輸入"
 else:
  stu_name = raw_input("請輸入學(xué)生姓名:")
  stu_class = input("請輸入學(xué)生班級:")
  sql1 = "INSERT INTO StudentTable(StuId,NAME,CLASS)"
  sql1 += " VALUES(%d,'%s',%d);"%(stu_id,stu_name,stu_class)
  cu.execute(sql1)
  cx.commit()
  print "恭喜你,學(xué)生錄入成功!"
 cu.close()


def xuanke():#學(xué)生選課
 cu = cx.cursor()
 stu_id = input('請輸入要選課的學(xué)生學(xué)號:')
 sql2 = "select StuId from StudentTable where StuId = %d;"%(stu_id)
 cu.execute(sql2)
 row = cu.fetchone()
 if row:
  sql3 = "select CourseId,Name,Teacher,Classroom, StartTime,EndTime from CourseTable"
  cu.execute(sql3)
  rows = cu.fetchall()
  for row in rows:
   print "CourseId = ", row[0]
   print "Name = ", row[1]
   print "Teacher = ", row[2]
   print "Classroom = ",row[3]
   print "StartTime = ",row[4]
   print "EndTime = ",row[5], "\n"
  cou_id = input("請輸入要選的課程號:")
  sql0 = "select CourseId from CourseTable where CourseId =%d;"%(cou_id)
  cu.execute(sql0)
  row = cu.fetchone()
  if row:
   sql = "select StuId CourseId from XuankeTable "
   sql += "where CourseId = %d and StuId=%d;"%(cou_id,stu_id)
   cu.execute(sql)
   rows = cu.fetchone()
   if row:
    print "該課程已選,不能重復(fù)選課!"
    break
   else:
    sql3 = "insert into XuankeTable (StuId,CourseId) values (%d,%d)"%(stu_id,cou_id)
    cu.execute(sql3)
    cx.commit()
    print "恭喜你，選課成功!"
  else:
   print "sorry,該課程不存在!"
 else:
  print "sorry,沒有該學(xué)生號"
 cu.close()



def stu_id_search():#按照學(xué)生學(xué)號查詢學(xué)生信息
 cu = cx.cursor()
 search_stu_id = input("請輸入要查詢的學(xué)號:")
 sql4 = "SELECT ID,StuId,NAME, CLASS from StudentTable "
 sql4 += "where StuId= %d;" % (search_stu_id)
 cu.execute(sql4)
 row = cu.fetchone()
 if row: 
  print
  print "您要查詢的學(xué)生信息為:"
  print "ID = ", row[0]
  print "StuId = ", row[1]
  print "NAME = ", row[2]
  print "CLASS = ",row[3], "\n"
 else:
  print "sorry,沒有該學(xué)生信息！"
 cu.close()


def stu_id_cou():#按照學(xué)生學(xué)號查詢該學(xué)生所選課程
 cu = cx.cursor()
 stu_id = input("請輸入要查詢學(xué)生號:")
 sql5 = "select StuId from StudentTable where StuId = %d;"%(stu_id)
 cu.execute(sql5)
 row = cu.fetchone()
 if row :
  sql6 = "select A.*,B.*,C.* from XuankeTable A, CourseTable B, StudentTable C \
  where A.StuId = %d and A.CourseId=B.CourseId and A.StuId=C.StuId"%(stu_id)#連表查詢
  cu.execute(sql6)
  rows = cu.fetchall()
  for row in rows:
   print "該學(xué)生所選課程為:"
   print "StuId=",row[1]
   print "CourseId=",row[2]
   print "Name = ", row[7]
   print "Teacher = ", row[8]
   print "Classroom = ",row[9]
   print "StartTime = " ,row[10]
   print "EndTime = ",row[11],"\n"
   print
 else:
  print "sorry,沒有該學(xué)生選課信息!"
 cu.close()


def cou_id_search(): #按照課程號查詢課程信息
 cu = cx.cursor()
 cou_id = input("請輸入要查詢的課程號:")
 sql7 = "select CourseId ,Name,Teacher,Classroom,StartTime,EndTime from CourseTable "
 sql7 += "where CourseId = %d;"%(cou_id)
 cu.execute(sql7)
 row = cu.fetchone()
 if row:
  print "您要查詢的課程信息為:"
  print "CourseId = ",row[0]
  print "Name = ", row[1]
  print "Teacher = ", row[2]
  print "Classroom = ",row[3]
  print "StartTime = " ,row[4]
  print "EndTime = ",row[5],"\n"
 else:
  print "sorry,沒有該課程信息!"
 cu.close()


def cou_id_stu():#按照課程號查詢選擇該課程的學(xué)生列表
 cu = cx.cursor()
 cou_id = input('請輸入課程號:')
 sql8 = "select CourseId from XuankeTable where CourseId =%d;"%(cou_id)
 cu.execute(sql8)
 row = cu.fetchone()
 if row:
  sql9 = "select A.*,B.*,C.* from XuankeTable A, CourseTable B, StudentTable C \
  where A.CourseId = %d and A.CourseId=B.CourseId and A.StuId=C.StuId"%(cou_id)
  cu.execute(sql9)
  rows = cu.fetchall()
  for row in rows:
   print
   print "選擇該課程的學(xué)生為:"
   print "StuId = ", row[1]
   print "CourseId = ", row[2]
   print "NAME = ", row[14]
   print "CLASS = ",row[15],"\n"
 else:
  print "sorry,沒有該課程信息!"
 cu.close()

def menu():
 print '1.進(jìn)入學(xué)生信息系統(tǒng)(學(xué)生信息錄入)'
 print '2.進(jìn)入學(xué)生選課系統(tǒng)(學(xué)生選課操作)'
 print '3.進(jìn)入學(xué)生選課信息系統(tǒng)(學(xué)生信息查詢和選課情況查詢)'
 print '4.退出程序'

def student():
 print '1.錄入學(xué)生信息'
 print '2.返回主菜單'

def Course():
 print '1.開始選課'
 print '2.返回主菜單'

def information():
 print '1.按學(xué)號查詢學(xué)生信息'
 print '2.按學(xué)號查看學(xué)生選課課程列表'
 print '3.按課程號查看課程信息'
 print '4.按課程號查看選課學(xué)生列表'
 print '5.返回主菜單'


while True:
 menu()
 print
 x = raw_input('請輸入您的選擇菜單號:')
 if x == '1':
  #進(jìn)入學(xué)生信息系統(tǒng)
  student()
  stu = raw_input('您已進(jìn)入學(xué)生錄入系統(tǒng),請再次輸入選擇菜單:')
  print
  if stu == '1':
   insert_stu()
   continue
  if stu == '2':
   continue
  else:
   print "輸入的選項不存在，請重新輸入！"
   continue

 if x == '2':
  #進(jìn)入選課信息系統(tǒng)
  Course()
  cou = raw_input('您已進(jìn)入學(xué)生選課系統(tǒng),請再次輸入選擇菜單:')
  print
  if cou == '1':
   xuanke()
   continue
  if cou == '2':
   continue
  else:
   print "輸入的選項不存在，請重新輸入！"
   continue

 if x == '3':
  #進(jìn)入學(xué)生選課信息表
  information()
  inf = raw_input('您已進(jìn)入學(xué)生選課信息系統(tǒng),請再次輸入選擇菜單:')
  print
  if inf == '1':
   stu_id_search()
   continue
  if inf == '2':
   stu_id_cou()
   continue
  if inf == '3':
   cou_id_search()
   continue
  if inf == '4':
   cou_id_stu()
   continue
  if inf == '5':
   continue
  else:
   print "輸入的選項不存在，請重新輸入！"
   continue

 if x == '4':
  print "謝謝使用！"
  exit()
 else:
  print "輸入的選項不存在，請重新輸入！"
  continue