#include "shared_grade_book_db.h" SCALE_LIST *global_scale_list = 0; CATEGORY_LIST *global_cat_list = 0; ITEM_LIST *global_item_list = 0; LETTER_GRADE_LIST *global_letter_grades =0; GRADE_LIST *global_grade_list = 0; GRADE_LIST *global_grade_temp_list = 0; char global_a_letter[MAX_GRADE_LEN +1]; double global_min = 0; double global_max = 0; double global_average = 0; static int callback(void *p_data, int num_fields, char **p_fields, char **p_col_name) { SCALE *ptr = 0; ptr = (SCALE *)malloc(sizeof(SCALE)); if(!ptr) cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db callback func"); ptr->id = atoi(p_fields[0]); strncpy(ptr->grade, p_fields[1], MAX_GRADE_LEN +1); ptr->start = atof(p_fields[2]); ptr->end = atof(p_fields[3]); ptr->count = 0; ptr->next = 0; if(!global_scale_list->head) global_scale_list->head = ptr; else global_scale_list->current->next = ptr; global_scale_list->current = ptr; return 0; } int get_scales(CONFIG_STRUCT *conf, const char *my_db) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_scale_list = (SCALE_LIST *)malloc(sizeof(SCALE_LIST)); global_scale_list->head = 0; global_scale_list->current = 0; ret = sqlite3_exec(db, "select * from Scales where grade != 'XXXXX'", callback, &nrecs, &errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_close(db); return 0; } sqlite3_close(db); return 1; } static int category_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name) { CATEGORY *ptr = 0; ptr = (CATEGORY *)malloc(sizeof(CATEGORY)); if(!ptr) cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db category callback func"); ptr->cat_id = atoi(p_fields[0]); strncpy(ptr->cat_name, p_fields[1], MAX_CAT_LEN +1); ptr->weight = atof(p_fields[2]); ptr->drop_lowest = atoi(p_fields[3]); ptr->number_members = 0; ptr->number_graded_items = 0; ptr->next = 0; if(!global_cat_list->head) global_cat_list->head = ptr; else global_cat_list->current->next = ptr; global_cat_list->current = ptr; return 0; } static int category_member_count(void *p_data, int num_fields, char **p_fields, char **p_col_name) { global_cat_list->current->number_members = atoi(p_fields[0]); return 0; } static int grades_entered_count(void *p_data, int num_fields, char **p_fields, char **p_col_name) { global_cat_list->current->number_graded_items = atoi(p_fields[0]); return 0; } static int grades_hided_count(void *p_data, int num_fields, char **p_fields, char **p_col_name) { global_cat_list->current->hided_items = atoi(p_fields[0]); return 0; } int get_categories(CONFIG_STRUCT *conf, const char *my_db) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; char *stmt; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_cat_list = (CATEGORY_LIST *)malloc(sizeof(CATEGORY_LIST)); global_cat_list->head = 0; global_cat_list->current = 0; ret = sqlite3_exec(db, "select * from Categories where catName != 'XXXXXXXXXXXX' order by catId", category_callback, &nrecs, &errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_close(db); return 0; } if(global_cat_list && global_cat_list->head) { for(global_cat_list->current = global_cat_list->head; global_cat_list->current; global_cat_list->current = global_cat_list->current->next) { stmt = sqlite3_mprintf("select count(*) from Items where catId=%d", global_cat_list->current->cat_id); if(stmt) { ret = sqlite3_exec(db, stmt, category_member_count, &nrecs, &errmsg); if(errmsg) { sqlite3_free(stmt); sqlite3_free(errmsg); sqlite3_close(db); return 0; } sqlite3_free(stmt); } } } if(global_cat_list && global_cat_list->head) { for(global_cat_list->current = global_cat_list->head; global_cat_list->current; global_cat_list->current = global_cat_list->current->next) { stmt = sqlite3_mprintf("select count(*) from Items where catId=%d and gradesEntered='yes'", global_cat_list->current->cat_id); if(stmt) { ret = sqlite3_exec(db, stmt, grades_entered_count, &nrecs, &errmsg); if(errmsg) { sqlite3_free(stmt); sqlite3_free(errmsg); sqlite3_close(db); return 0; } sqlite3_free(stmt); } } } if(global_cat_list && global_cat_list->head) { for(global_cat_list->current = global_cat_list->head; global_cat_list->current; global_cat_list->current = global_cat_list->current->next) { stmt = sqlite3_mprintf("select count(*) from Items where catId=%d and hide='yes'", global_cat_list->current->cat_id); if(stmt) { ret = sqlite3_exec(db, stmt, grades_hided_count, &nrecs, &errmsg); if(errmsg) { sqlite3_free(stmt); sqlite3_free(errmsg); sqlite3_close(db); return 0; } sqlite3_free(stmt); } } } sqlite3_close(db); return 1; } static int item_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name) { ITEM *ptr = 0; ptr = (ITEM *)malloc(sizeof(ITEM)); if(!ptr) cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db category callback func"); ptr->item_id = atoi(p_fields[0]); strncpy(ptr->item_name, p_fields[1], MAX_ITEM_LEN +1); ptr->points = atof(p_fields[2]); ptr->due_date = atoi(p_fields[3]); strncpy(ptr->extra_credit, p_fields[4], MAX_EXTRA_CREDIT_LEN +1); strncpy(ptr->is_letter_grade, p_fields[5], MAX_BOOLEN_LEN +1); strncpy(ptr->grade_entered, p_fields[6], MAX_BOOLEN_LEN +1); strncpy(ptr->hide, p_fields[7], MAX_BOOLEN_LEN +1); ptr->cat_id = atoi(p_fields[8]); ptr->grades = 0; ptr->letters = 0; *(ptr->min) = '\0'; *(ptr->max) = '\0'; *(ptr->avg) = '\0'; ptr->next = 0; if(!global_item_list->head) global_item_list->head = ptr; else global_item_list->current->next = ptr; global_item_list->current = ptr; return 0; } int get_cat_members(CONFIG_STRUCT *conf, int cat_id, const char *my_db) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; char *stmt; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_item_list = (ITEM_LIST *)malloc(sizeof(ITEM_LIST)); global_item_list->head = 0; global_item_list->current = 0; stmt = sqlite3_mprintf("select * from Items where catId=%d", cat_id); if(stmt) { ret = sqlite3_exec(db, stmt,item_callback,&nrecs,&errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_free(stmt); sqlite3_close(db); return 0; } sqlite3_free(stmt); } sqlite3_close(db); return 1; } int get_items(CONFIG_STRUCT *conf, const char *my_db, int desc) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_item_list = (ITEM_LIST *)malloc(sizeof(ITEM_LIST)); global_item_list->head = 0; global_item_list->current = 0; if(desc == 1) ret = sqlite3_exec(db, "select * from Items where itemName != 'XXXXXXXXXXXX' order by catId, dueDate desc", item_callback, &nrecs, &errmsg); else ret = sqlite3_exec(db, "select * from Items where itemName != 'XXXXXXXXXXXX' order by catId, dueDate asc", item_callback, &nrecs, &errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_close(db); return 0; } sqlite3_close(db); return 1; } int get_one_item(CONFIG_STRUCT *conf, int item_id, const char *my_db) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; char *stmt =0; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_item_list = (ITEM_LIST *)malloc(sizeof(ITEM_LIST)); global_item_list->head = 0; global_item_list->current = 0; stmt = sqlite3_mprintf("select * from Items where itemId=%d", item_id); if(stmt) { ret = sqlite3_exec(db, stmt, item_callback, &nrecs, &errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_free(stmt); sqlite3_close(db); return 0; } sqlite3_free(stmt); } sqlite3_close(db); return 1; } static int letter_grade_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name) { LETTER_GRADE *ptr = 0; ptr = (LETTER_GRADE *)malloc(sizeof(LETTER_GRADE)); if(!ptr) cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db letter grade callback func"); ptr->letter_id = atoi(p_fields[0]); strncpy(ptr->letter, p_fields[1], MAX_GRADE_LEN +1); ptr->points = atof(p_fields[2]); ptr->item_id = atoi(p_fields[3]); ptr->count = 0; ptr->next = 0; if(!global_letter_grades->head) global_letter_grades->head = ptr; else global_letter_grades->current->next = ptr; global_letter_grades->current = ptr; return 0; } int get_letter_grades(CONFIG_STRUCT *conf, int item_id, const char *my_db) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; char *stmt =0; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, my_db); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_letter_grades = (LETTER_GRADE_LIST *)malloc(sizeof(LETTER_GRADE_LIST)); global_letter_grades->head = 0; global_letter_grades->current = 0; stmt = sqlite3_mprintf("select * from letterGrades where itemId=%d order by points desc", item_id); if(stmt) { ret = sqlite3_exec(db, stmt, letter_grade_callback, &nrecs, &errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_free(stmt); sqlite3_close(db); return 0; } sqlite3_free(stmt); } return 1; } static int grade_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name) { GRADE *ptr = 0; ptr = (GRADE *)malloc(sizeof(GRADE)); if(!ptr) cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db grade callback func"); strncpy(ptr->username, p_fields[0], MAX_USERNAME +1); ptr->item_id = atoi(p_fields[1]); ptr->score = atof(p_fields[2]); strncpy(ptr->excuse, p_fields[3], MAX_BOOLEN_LEN +1); strncpy(ptr->pending, p_fields[4], MAX_BOOLEN_LEN +1); ptr->comment = malloc_str(p_fields[5]); ptr->letter_id = atoi(p_fields[6]); ptr->timestamp = atoi(p_fields[7]); strncpy(ptr->is_letter_grade, p_fields[8], MAX_BOOLEN_LEN +1); strncpy(ptr->drop, "no", MAX_BOOLEN_LEN +1); ptr->next = 0; if(!global_grade_list->head) global_grade_list->head = ptr; else global_grade_list->current->next = ptr; global_grade_list->current = ptr; return 0; } int get_current_grade_list(CONFIG_STRUCT *conf, int item_id, const char *mydb) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; char *stmt =0; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_grade_list = (GRADE_LIST *)malloc(sizeof(GRADE_LIST)); global_grade_list->head = 0; global_grade_list->current = 0; stmt = sqlite3_mprintf("select G.username, G.itemId, G.earnPoints, G.excuse, G.isPending, G.comments, G.letterId, G.timestamp, isLetterGrade from Grades G, Items, (select max(timestamp) as maxDate, username, itemId from Grades where itemId=%d group by username, itemId) M where G.username = M.username and G.timestamp = M.maxDate and G.itemId=Items.itemId", item_id); if(stmt) { ret = sqlite3_exec(db, stmt,grade_callback,&nrecs,&errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_free(stmt); sqlite3_close(db); cs_critical_error(ERR_GRADES_DB, "get current grade list"); return 0; } sqlite3_free(stmt); } sqlite3_close(db); return 1; } int get_user_score_logs(CONFIG_STRUCT *conf, int item_id, const char *username, const char *mydb) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; char *stmt =0; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_grade_list = (GRADE_LIST *)malloc(sizeof(GRADE_LIST)); global_grade_list->head = 0; global_grade_list->current = 0; stmt = sqlite3_mprintf("select G.username, G.itemId, G.earnPoints, G.excuse, G.isPending, G.comments, G.letterId, G.timestamp, isLetterGrade from Grades G, Items where G.itemId=%d and G.itemId=Items.itemId and G.username='%s'", item_id, username); if(stmt) { ret = sqlite3_exec(db, stmt,grade_callback,&nrecs,&errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_free(stmt); sqlite3_close(db); cs_critical_error(ERR_GRADES_DB, "get current grade list"); return 0; } sqlite3_free(stmt); } sqlite3_close(db); return 1; } static int a_letter_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name) { strncpy(global_a_letter, p_fields[0], MAX_GRADE_LEN +1); return 0; } int update_letter_log_list(CONFIG_STRUCT *conf, int item_id, const char *mydb, GRADE_LIST *list) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; char *stmt =0; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) cs_critical_error(ERR_GRADES_DB, "get grade list"); if(list && list->head) { for(list->current = list->head; list->current; list->current = list->current->next) { stmt = sqlite3_mprintf("select letter from LetterGrades where itemId=%d and letterId=%d", item_id, list->current->letter_id); if(stmt) { ret = sqlite3_exec(db, stmt, a_letter_callback,&nrecs,&errmsg); if(errmsg) { sqlite3_free(stmt); sqlite3_free(errmsg); sqlite3_close(db); cs_critical_error(ERR_GRADES_DB, "get a letter grade error"); } strncpy(list->current->letter, global_a_letter, MAX_GRADE_LEN +1); sqlite3_free(stmt); } } } sqlite3_close(db); return 1; } static int grade_temp_callback(void *p_data, int num_fields, char **p_fields, char **p_col_name) { GRADE *ptr = 0; ptr = (GRADE *)malloc(sizeof(GRADE)); if(!ptr) cs_critical_error(ERR_MALLOC_FAILED, "shared_grade_book_db grade callback func"); strncpy(ptr->username, p_fields[0], MAX_USERNAME +1); ptr->item_id = atoi(p_fields[1]); ptr->score = atof(p_fields[2]); strncpy(ptr->excuse, p_fields[3], MAX_BOOLEN_LEN +1); strncpy(ptr->pending, p_fields[4], MAX_BOOLEN_LEN +1); ptr->letter_id = atoi(p_fields[5]); ptr->timestamp = atoi(p_fields[6]); strncpy(ptr->drop, "no", MAX_BOOLEN_LEN +1); if(strlen(p_fields[7])) ptr->comment = malloc_str(p_fields[7]); else ptr->comment =0; ptr->next = 0; if(!global_grade_temp_list->head) global_grade_temp_list->head = ptr; else global_grade_temp_list->current->next = ptr; global_grade_temp_list->current = ptr; return 0; } int get_one_item_grade_list(CONFIG_STRUCT *conf, int item_id, const char *mydb) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; char *stmt =0; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_grade_temp_list = (GRADE_LIST *)malloc(sizeof(GRADE_LIST)); global_grade_temp_list->head = 0; global_grade_temp_list->current = 0; stmt = sqlite3_mprintf("select G.username, G.itemId, G.earnPoints, G.excuse, G.isPending, G.letterId , G.timestamp, G.comments from Grades G, (select max(timestamp) as maxDate, username, itemId from Grades where itemId=%d group by username, itemId) M where G.username = M.username and G.timestamp = M.maxDate", item_id); if(stmt) { ret = sqlite3_exec(db, stmt,grade_temp_callback,&nrecs,&errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_free(stmt); sqlite3_close(db); cs_critical_error(ERR_GRADES_DB, "get one item grade list"); return 0; } sqlite3_free(stmt); } sqlite3_close(db); return 1; } int get_student_one_item_grade(CONFIG_STRUCT *conf, int item_id, const char *username, const char *mydb) { int ret, nrecs = 0; char *errmsg = 0; sqlite3 *db; char db_name[MAX_PATH +1]; char *stmt =0; snprintf(db_name, MAX_PATH +1, "%s%s/%s", conf->course_path, GRADE_BOOK_DIR, mydb); if(!file_exists(db_name)) return 0; ret = sqlite3_open(db_name, &db); if(ret) return 0; global_grade_list = (GRADE_LIST *)malloc(sizeof(GRADE_LIST)); global_grade_list->head = 0; global_grade_list->current = 0; stmt = sqlite3_mprintf("select G.username, G.itemId, G.earnPoints, G.excuse, G.isPending, G.comments, G.letterId, G.timestamp, isLetterGrade from Grades G, Items, (select max(timestamp) as maxDate, username, itemId from Grades where itemId=%d group by username, itemId) M where G.username='%s' and G.username = M.username and G.timestamp = M.maxDate and G.itemId=Items.itemId", item_id, username); if(stmt) { ret = sqlite3_exec(db, stmt,grade_callback,&nrecs,&errmsg); if(errmsg) { sqlite3_free(errmsg); sqlite3_free(stmt); sqlite3_close(db); cs_critical_error(ERR_GRADES_DB, "get current grade list"); return 0; } sqlite3_free(stmt); } sqlite3_close(db); return 1; }