#include "public.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>

char host_name[256] = "localhost";
char user[256] = "123";
char pass[256] = "456";
char dbase[256] = "who";
unsigned short port = 3306;


char *f_gets(char *str, int size)
{
    char *t=fgets(str,size,stdin);
    char *find;
    if(t)
    {
        find=strchr(str,'\n');
        if(find)
            *find='\0';
        else
        {
            while(getchar()!='\n')
                continue;
        }
    }
    else
        return NULL;
    return str;

}

void display_all_course(void)
{
    MYSQL mysql;
    MYSQL_RES *result;
    MYSQL_ROW row;
    if(mysql_init(&mysql)==NULL)
    {
        printf("初始化数据库失败\n");
    }
    if(!mysql_real_connect(&mysql,host_name,user, pass, dbase, port, NULL, 0))
    {
        printf("连接数据库失败\n");
    }
    char *sql="select * from course";
     if(mysql_real_query(&mysql, sql, strlen(sql))==0)
    {
        // printf("这个学生这门课的平均成绩\n");
        result = mysql_store_result(&mysql);
        printf("title\t credit\n");
        printf("---------------------------------------\n");
        while (row = mysql_fetch_row(result))
        {
            printf("%s\t%.2lf\n", row[1], atof(row[2]));
        }
        printf("---------------------------------------\n");
        mysql_free_result(result);
    }
    else
    {
        printf("查询出错\n");
    }
    mysql_close(&mysql);
}

void start_choose_the_course(int *user_id)
{
    MYSQL mysql;
    if(mysql_init(&mysql)==NULL)
    {
        printf("初始化数据库失败\n");
    }
    if(!mysql_real_connect(&mysql,host_name,user, pass, dbase, port, NULL, 0))
    {
        printf("连接数据库失败\n");
    }
    int course_id;
    printf("请输入您相选择的课程的编号：\n");
    scanf("%d",&course_id);

    char *t1="insert into option_course(course_id,student_id) values(";
    char sql[256];
    memset(sql,0,sizeof(sql));

    sprintf(sql,"%s%d%c%d%c",t1,course_id,',',*user_id,')');
    if(mysql_query(&mysql,sql))
    {
        printf("选课失败\n");
        printf("您选择了不存在的课程或者您已选择过该课程\n");
    }
    else
    {
        printf("选课成功\n");
    }
    mysql_close(&mysql);
}

void display_all_option_course(int *user_id)
{
    MYSQL mysql;
    MYSQL_RES *result;
    MYSQL_ROW row;
    if(mysql_init(&mysql)==NULL)
    {
        printf("初始化数据库失败\n");
    }
    if(!mysql_real_connect(&mysql,host_name,user, pass, dbase, port, NULL, 0))
    {
        printf("连接数据库失败\n");
    }
    char *t1="select t2.id,t2.title,t2.credit,r.localtion from room r join\
(select c.id id,c.title title,c.credit credit from course c join\
(select course_id course_id from option_course where student_id=";
    char *t2=") t on t.course_id=c.id ) t2 on t2.id=r.course_id";
    char sql[256];
    memset(sql,0,sizeof(sql));
    sprintf(sql,"%s%d%s",t1,*user_id,t2);

    if(mysql_real_query(&mysql, sql, strlen(sql))==0)
    {
        printf("该学生的所有已选课程：\n");
        result = mysql_store_result(&mysql);
        printf("course_id\t title\t\tcredit\tlocation\n");
        printf("----------------------------------------------------------\n");
        while (row = mysql_fetch_row(result))
        {
            printf("%s\t\t%s\t%.2lf\t%s\n", row[0], row[1],atof(row[2]),row[3]);
        }
        printf("----------------------------------------------------------\n");
        mysql_free_result(result);
    }
    else
    {
        printf("查询出错\n");
        return ;
    }
    mysql_close(&mysql);
}

void display_menu(void)
{
    printf("a) 显示所有选修课\n\
b) 选课\n\
c) 查看自己已选课程\n\
q) 退出\n");
}

bool t_login(char *t_ID_num)
{
    bool flag;
    MYSQL mysql;
    MYSQL_RES *result;
    MYSQL_ROW row;
    char *h = getenv("DBHOST");
    char *u = getenv("DBUSER");
    char *pwd = getenv("DBPASS");
    char *base = getenv("DBNAME");
    char *ap = getenv("DBPORT");

    if(h)
    {
	    strcpy(host_name, h);
    }
    if(u)
    {
	    strcpy(user, u);
    }
    if(pwd)
    {
    	strcpy(pass, pwd);
    }
    if(base)
    {
        strcpy(dbase, base);
    }
    if(ap)
    {
	port = atoi(ap);
    }
    printf("mysql host: %s, user: %s, pass: ***, dbase: %s, port: %d\r\n", host_name, user, dbase, port);
    if(mysql_init(&mysql)==NULL)
    {
        printf("初始化数据库失败\n");
        return false;
    }
    if(!mysql_real_connect(&mysql,host_name,user, pass, dbase, port, NULL, 0))
    {
        printf("连接数据库失败\n");
        return false;
    }
    char ID_num[20];
    memset(ID_num,0,sizeof(ID_num));
    printf("请输入您的教师编号:\n");
    f_gets(ID_num,sizeof(ID_num));


    char pass_word[64];
    memset(pass_word,0,sizeof(pass_word));
    printf("请输入您的密码:\n");
    f_gets(pass_word,sizeof(pass_word));

    char sql[256];
    memset(sql,0,sizeof(sql));
    char *t1="select * from teacher where ID_num=";
// select * from teacher where ID_num='20000010';
    sprintf(sql,"%s%c%s%c",t1,'"',ID_num,'"');
    if (mysql_real_query(&mysql, sql, strlen(sql)))
    {
        printf("查询失败\n");
        return false;
    }
    else
    {
        result = mysql_store_result(&mysql);
        while (row = mysql_fetch_row(result))
        {
            if(strcmp(pass_word,row[3])==0)
            {
                printf("教师%s 欢迎登陆\n",row[1]);
                strcpy(t_ID_num,row[2]);
                // printf("%s\n",t_ID_num);
                flag=true;
            }
            else
            {
                printf("您输入的密码错误\n");
                flag=false;
            }
        }
        printf("---------------------------------------\n");
        mysql_free_result(result);
    }
    mysql_close(&mysql);
    return flag;
}


void show_the_taught_course(char *ID_num)
{
    MYSQL mysql;
    MYSQL_RES *result;
    MYSQL_ROW row;
    if(mysql_init(&mysql)==NULL)
    {
        printf("初始化数据库失败\n");
    }
    if(!mysql_real_connect(&mysql,host_name,user, pass, dbase, port, NULL, 0))
    {
        printf("连接数据库失败\n");
    }
    char sql[512];
    memset(sql,0,sizeof(sql));
    char *t1="select t4.id,t4.name,t4.title,r.localtion \
from (select c.id id,t2.name name,c.title title from course c join \
(select t.name name, t1.course_id course_id from teacher t,t_course t1 \
where t.ID_num=";
    char *t2=" and t.id=t1.teacher_id) t2 on t2.course_id=c.id) t4 join room r on t4.id=r.course_id";
    sprintf(sql,"%s%c%s%c%s",t1,'"',ID_num,'"',t2);
    // printf("%s\n",sql);
// select t4.id,t4.name,t4.title,r.localtion
// from 
// (select c.id id,t2.name name,c.title title
// from course c
// join 
// (select t.name name, t1.course_id course_id
// from teacher t,t_course t1
// where t.ID_num='20000010' and t.id=t1.teacher_id) t2
// on t2.course_id=c.id) t4
// join room r
// on t4.id=r.course_id;
    if(mysql_real_query(&mysql, sql, strlen(sql))==0)
    {
        result = mysql_store_result(&mysql);
        printf("course_id\t t.name\t\ttitle\tlocation\n");
        printf("----------------------------------------------------------\n");
        while (row = mysql_fetch_row(result))
        {
            printf("%s\t%s\t%s\t%s\n", row[0], row[1],row[2],row[3]);
        }
        printf("----------------------------------------------------------\n");
        mysql_free_result(result);
    }
    else
    {
        printf("查询出错\n");
        return ;
    }
    mysql_close(&mysql);
}

void show_who_choose_t_course(void)
{
    MYSQL mysql;
    MYSQL_RES *result;
    MYSQL_ROW row;
    if(mysql_init(&mysql)==NULL)
    {
        printf("初始化数据库失败\n");
    }
    if(!mysql_real_connect(&mysql,host_name,user, pass, dbase, port, NULL, 0))
    {
        printf("连接数据库失败\n");
    }
    int course_id;
    printf("请输入您想查询的课程编号：");
    scanf("%d",&course_id);
    char sql[256];
    memset(sql,0,sizeof(sql));
    char *t1="select s.id,s.name,s.ID_num,s.sex from student s join \
(select student_id id from option_course where course_id=";
    char *t2=") t on t.id=s.id";
    sprintf(sql,"%s%d%s",t1,course_id,t2);
// select s.id,s.name,s.ID_num,s.sex 
// from student s
// join 
// (select student_id id from option_course where course_id=13) t
// on t.id=s.id;
    int count=0;
    if(mysql_real_query(&mysql, sql, strlen(sql))==0)
    {
        printf("选择该课程的学生有：\n");
        result = mysql_store_result(&mysql);
        printf("s.id\t t.name\tID_num\tsex\n");
        printf("----------------------------------------------------------\n");
        while (row = mysql_fetch_row(result))
        {
            printf("%s\t%s\t%s\t%s\n", row[0], row[1],row[2],row[3]);
            count++;
        }
        printf("----------------------------------------------------------\n");
        mysql_free_result(result);
        mysql_close(&mysql);
        printf("选择该课程的总人数为：%d",count);

    }
    else
    {
        printf("查询出错\n");
        return ;
    }
}

void show_the_count_student(int *course_id)
{
    MYSQL mysql;
    MYSQL_RES *result;
    MYSQL_ROW row;
    if(mysql_init(&mysql)==NULL)
    {
        printf("初始化数据库失败\n");
    }
    if(!mysql_real_connect(&mysql,host_name,user, pass, dbase, port, NULL, 0))
    {
        printf("连接数据库失败\n");
    }
    char sql[256];
    memset(sql,0,sizeof(sql));
    char *t1="select count(t1.id) from (select s.id id,s.name,s.ID_num,s.sex from student s join \
(select student_id id from option_course where course_id=";
    char *t2=") t on t.id=s.id) t1";
    sprintf(sql,"%s%d%s",t1,*course_id,t2);
// select count(t1.id)
// from
// (select s.id id,s.name,s.ID_num,s.sex 
// from student s
// join 
// (select student_id id from option_course where course_id=13) t
// on t.id=s.id) t1;
    if(mysql_real_query(&mysql, sql, strlen(sql))==0)
    {
        while (row = mysql_fetch_row(result))
        {
            printf("%d\n", atoi(row[0]));
        }
        mysql_free_result(result);
    }
    else
    {
        printf("查询出错\n");
        return ;
    }
    mysql_close(&mysql);
}