void get_bd(http_request_t req, socket_t * clientSocket,db_t * db)
{
char text[20];
strcpy(text,req.uri+4);
int count= db_count(db,text);
char buf[10000];
char tmp[1000];
sqlite3_stmt * stmt = NULL;
strcpy(buf,"<html>"
"<head>"
"<title>Page Title</title>"
"</head>"
"<body>"
"<table border=\"3\" align=\"center\">"
"<tr>"
"<th>NAME</th>"
"<th>SURNAME</th>"
"<th>AGE</th>"
"</tr>");
for(int i=0;i<count;i++)
{
stmt=db_getrow(db,text,i);
sprintf(tmp,"<tr>"
"<th><a href=\"/db/%s/%i\">%s</a></th>"
"<th><a href=\"/db/%s/%i\">%s</a></th>"
"<th><a href=\"/db/%s/%i\">%i</a></a></th>"
"</tr>",text,i,sqlite3_column_text(stmt, 0),text,i,sqlite3_column_text(stmt, 1),text,i,sqlite3_column_int(stmt, 2));
strcat(buf,tmp);
sqlite3_finalize(stmt);
}
strcat(buf,"</table>");
sprintf(tmp,"<p align=\"center\">Count: %i<br><br><br></p>"
"<p align=\"center\"><a href=\"/db/paste/%s\">Insert new row</a></p>"
"<p align=\"center\"><a href=\"/db\">To Databases</a></p>",count,text);
strcat(buf,tmp);
strcat(buf, "</body>"
"</html>");
socket_write_string(clientSocket,buf);
}
void server_mastersByID(socket_t * client, http_request_t * req, db_t * db)
{
char buffer[10240] = "";
char * point = strstr(req->uri, "/api/ScrumMasters/");
point += 18;
int index = atoi(point);
if(0 < index && index < db_count(db) + 1)
{
if (strcmp(req->method, "GET") == 0)
{
master_t * master = db_getDataById(db, index - 1);
cJSON * jText = cJSON_CreateObject();
cJSON_AddItemToObject(jText, "Name", cJSON_CreateString(master_getName(master)));
cJSON_AddItemToObject(jText, "Surname", cJSON_CreateString(master_getSurname(master)));
cJSON_AddItemToObject(jText, "CountK", cJSON_CreateNumber(master_getCountK(master)));
cJSON_AddItemToObject(jText, "CountP", cJSON_CreateNumber(master_getCountP(master)));
char * pageText = cJSON_Print(jText);
free(master);
char * textJSON = textToJSON(pageText);
strcat(buffer, textJSON);
free(textJSON);
}
else if (strcmp(req->method, "DELETE") == 0)
{
master_t * master = db_getDataById(db, index - 1);
db_deleteData(db, master_getID(master));
free(master);
cJSON * jText = cJSON_CreateObject();
cJSON_AddItemToObject(jText, "Success", cJSON_CreateString("Successfully deleted"));
char * pageText = cJSON_Print(jText);
char * textJSON = textToJSON(pageText);
strcat(buffer, textJSON);
free(textJSON);
}
}
else
{
cJSON * jText = cJSON_CreateObject();
cJSON_AddItemToObject(jText, "Error", cJSON_CreateString("ID Not Found"));
char * pageText = cJSON_Print(jText);
char * textJSON = textToJSON(pageText);
strcat(buffer, textJSON);
free(textJSON);
}
socket_write_string(client, buffer);
socket_close(client);
}
void parse_from_db(list_t* list,db_t* db)
{
sqlite3_stmt * stmt=NULL;
lanser * lan=Freelanser_new();
for(int i=0; i<db_count(db,"database"); i++)
{
lanser * lan=Freelanser_new();
stmt=db_getrow(db,"database",i);
Freelanser_set(lan,sqlite3_column_text(stmt, 0),sqlite3_column_text(stmt, 1),sqlite3_column_double(stmt, 2),sqlite3_column_text(stmt, 4),sqlite3_column_int(stmt, 3));
list_push(list,lan,get_size());
free(lan);
}
Freelanser_free(lan);
}
void server_Sort(http_request_t req,socket_t* clientSocket,db_t* self)
{
char* key;
char *com;
char buf[500]="SELECT * FROM database WHERE ";
char* tmp=strchr(req.uri,'?')+1;
key=strtok(tmp,"&");
list_t* list=list_new();
int count;
while(key!=NULL)
{
list_push(list,key,strlen(key)+1);
key=strtok(NULL,"&");
}
count=db_count(self,"database");
while(list_get_count(list))
{
com=list_pop(list);
if(!strncmp(com,"age",3))
{
if(strpbrk(com,"0987654321"))
{
char * qw=strstr(com,"_")+1;
if(!strncmp(qw,"gt",2))
{
sprintf(com,"age<%i",atoi(strpbrk(com,"0123456789")));
}
else if(!strncmp(qw,"lt",2))
{
sprintf(com,"age>%i",atoi(strpbrk(com,"0123456789")));
}
else
{
parse_from_db(list,self);
server_GET_all(req,clientSocket,list);
return;
}
}
else
{
parse_from_db(list,self);
server_GET_all(req,clientSocket,list);
return;
}
strcat(buf,com);
if(list_get_count(list))
strcat(buf," AND ");
}
else if(!strncmp(com,"salary",6))
{
if(strpbrk(com,"0987654321"))
{
char * qw=strstr(com,"_")+1;
if(!strncmp(qw,"gt",2))
{
sprintf(com,"salary<%.2f",atof(strpbrk(com,"0123456789")));
}
else if(!strncmp(qw,"lt",2))
{
sprintf(com,"salary>%.2f",atof(strpbrk(com,"0123456789")));
}
else
{
parse_from_db(list,self);
server_GET_all(req,clientSocket,list);
return;
}
}
else
{
parse_from_db(list,self);
server_GET_all(req,clientSocket,list);
return;
}
strcat(buf,com);
if(list_get_count(list))
strcat(buf," AND ");
}
else if(!strncmp(com,"count",5))
{
if(strpbrk(com,"0987654321"))
{
count=atoi(strpbrk(com,"0123456789"));
}
else
{
parse_from_db(list,self);
server_GET_all(req,clientSocket,list);
return;
}
}
}
sqlite3_stmt * stmt = NULL;
sqlite3_prepare_v2(get_db(self), buf, strlen(buf), &stmt, NULL);
int i=0;
puts(buf);
while(1)
{
int rc = sqlite3_step(stmt);
if (SQLITE_ROW == rc)
{
if(i<count)
{
lanser * FL=Freelanser_new();
Freelanser_set(FL,sqlite3_column_text(stmt, 0),sqlite3_column_text(stmt, 1),sqlite3_column_double(stmt, 2),sqlite3_column_text(stmt, 4),sqlite3_column_int(stmt, 3));
list_push(list,FL,get_size());
free(FL);
}
else
break;
i++;
}
else if (SQLITE_DONE == rc)
{
break;
}
}
server_GET_all(req,clientSocket,list);
}
int main(int argc, char **argv) {
DBHANDLE db;
int rc,c, flags, len;
char *ptr_command, *ptr_key, *find;
char *command, *key, *value;
command = key = value = NULL;
char *help_cmd = "command list:\n [add | update] key value \n [del | find] key \n [open | close] dbname\n [help count iterate exit]";
flags = O_RDWR;
while ((c = getopt(argc, argv, "nt?")) != -1) {
switch (c) {
case 'n':
flags |= O_CREAT;
break;
case 't':
flags |= O_TRUNC;
break;
case '?':
err_quit("usage: [-n] create new file [-t] truncate db <filename>");
break;
}
}
if (optind != argc - 1) {
err_quit("usage: [-n] create new file [-t] truncate db <filename>");
}
if ((db = db_open(argv[optind], flags, FILE_MODE)) == NULL) {
err_sys("db_open %s error", argv[optind]);
}
for (;;) {
char buf[MAX_LEN];
printf("APUEDB> ");
if (fgets(buf, MAX_LEN, stdin) == NULL) {
printf("Bye~\n");
exit(1);
}
to_lowercase(buf);
len = strlen(buf);
if ((ptr_command = strchr(buf,COMMAND_SEP)) == NULL) {
buf[len-1] = '\0';
if (strcmp(buf,"help") == 0) {
printf("%s\n", help_cmd);
continue;
} else if (strcmp(buf, "count") == 0) {
printf("%d\n", db_count(db));
} else if (strcmp(buf, "iterate") == 0) {
char *ptr;
db_rewind(db);
while ((ptr = db_nextrec(db, NULL)) != NULL) {
printf("%s\n", ptr);
}
continue;
} else if (strcmp(buf, "exit") == 0) {
printf("Bye~\n");
exit(1);
} else {
printf("invalid command, use command help for help\n");
continue;
}
}
command = strndup(buf, ptr_command - buf);
if (strcmp(command, "add") == 0) {
if ((ptr_key = strchr(ptr_command+1, COMMAND_SEP)) == NULL) {
printf("invalid add command, usage: add key value\n");
} else {
key = strndup(ptr_command+1, ptr_key - ptr_command - 1);
value = strndup(ptr_key+1, buf + len - 1 - ptr_key - 1);
rc = db_store(db, key, value, DB_INSERT);
if (rc == 0) {
printf("add +OK\n");
} else {
printf("key %s already exist\n", key);
}
}
} else if (strcmp(command, "update") == 0) {
if ((ptr_key = strchr(ptr_command+1, COMMAND_SEP)) == NULL) {
printf("invalid add command, usage: add key value\n");
} else {
key = strndup(ptr_command+1, ptr_key - ptr_command - 1);
value = strndup(ptr_key+1, buf + len - 1 - ptr_key - 1);
rc = db_store(db, key, value, DB_REPLACE);
if (rc == 0) {
printf("update +OK\n");
} else {
printf("key %s not found\n", key);
}
}
} else if (strcmp(command, "del") == 0) {
if ((ptr_key = strchr(ptr_command+1, COMMAND_SEP)) != NULL) {
printf("invalid del command, usage: del key\n");
continue;
}
key = strndup(ptr_command+1, buf + len - 1 - ptr_command -1);
rc = db_delete(db, key);
if (rc == 0) {
printf("del +OK\n");
} else {
printf("key %s not found\n", key);
}
} else if (strcmp(command, "find") == 0) {
if ((ptr_key = strchr(ptr_command+1, COMMAND_SEP)) != NULL) {
printf("invalid find command, usage: find key\n");
continue;
}
key = strndup(ptr_command+1, buf + len - 1 - ptr_command -1);
find = db_fetch(db, key);
if (find != NULL) {
printf("%d,%s\n", strlen(find), find);
} else {
printf("key %s not found\n", key);
}
}
if (key != NULL) {
free(key);
key = NULL;
}
if (command != NULL) {
free(command);
command = NULL;
}
if (value != NULL) {
free(value);
value = NULL;
}
}
}
void server_mastersHtmlByID(socket_t * client, http_request_t * req, db_t * db)
{
char buffer[10240] = "";
char * point = strstr(req->uri, "/ScrumMasters/");
point += 14;
int index = atoi(point);
if(0 < index && index < db_count(db) + 1)
{
if (strcmp(req->method, "GET") == 0)
{
char pageText[10240] = "";
master_t * master = db_getDataById(db, index - 1);
char text[1024] = "";
sprintf(text,
"<p>"
"Name: %s<br>"
"Surname: %s<br>"
"CountK: %i<br>"
"CountP: %i<br>"
"</p>",
master_getName(master),
master_getSurname(master),
master_getCountK(master),
master_getCountP(master));
strcat(pageText, text);
free(master);
char * pageTextLink = "<a href=\"#\" onclick=\"doDelete()\"/>Delete Scrum Master<br><br></a>\n";
strcat(pageText, pageTextLink);
char pageTextScript[1024];
sprintf(pageTextScript,
"<script>"
"function doDelete() {"
"var xhttp = new XMLHttpRequest();"
"xhttp.open(\"DELETE\", \"http://127.0.0.1:5000/ScrumMasters/%i\", true);"
"xhttp.send();"
"}"
"</script>", index);
strcat(pageText, pageTextScript);
char * textHTML = textToHTML(pageText);
strcat(buffer, textHTML);
free(textHTML);
}
else if (strcmp(req->method, "DELETE") == 0)
{
master_t * master = db_getDataById(db, index - 1);
db_deleteData(db, master_getID(master));
free(master);
char * pageText = "<h4>Successfully deleted</h4>";
sprintf(buffer,
"HTTP/1.1 200 OK\n"
"Content-Type: text/html\n"
"Content-Length: %zu\n"
"Access-Control-Allow-Origin: *\n"
"Access-Control-Allow-Methods: DELETE\n"
"\n%s",
strlen(pageText), pageText);
}
}
else
{
char * pageText = "<h4>ID Not Found</h4>";
char * textHTML = textToHTML(pageText);
strcat(buffer, textHTML);
free(textHTML);
}
socket_write_string(client, buffer);
socket_close(client);
}
void server_mastersHtml(socket_t * client, http_request_t * req, db_t * db)
{
char buffer[10240] = "";
if (strcmp(req->method, "GET") == 0)
{
char pageText[10240] = "";
for(int i = 0; i < db_count(db); i++)
{
master_t * master = db_getDataById(db, i);
char text[100] = "";
sprintf(text, "<a href=\"http://127.0.0.1:5000/ScrumMasters/%i\">%s %s<br></a>\n", i + 1, master_getSurname(master), master_getName(master));
strcat(pageText, text);
master_free(master);
}
char * text = "<a href=\"http://127.0.0.1:5000/new-ScrumMaster\"><br>New Scrum Master</a>";
strcat(pageText, text);
char * textHTML = textToHTML(pageText);
strcat(buffer, textHTML);
free(textHTML);
}
else if (strcmp(req->method, "POST") == 0)
{
char * name = (char *)http_request_getArg(req, "name");
char * surname = (char *)http_request_getArg(req, "surname");
char * countK = (char *)http_request_getArg(req, "countK");
char * countP = (char *)http_request_getArg(req, "countP");
if(verification(name, surname, countK, countP) == 1)
{
db_insertData(db, name, surname, atoi(countK), atoi(countP));
char pageText[1024] = "";
sprintf(pageText,
"<p>"
"Name: %s<br>"
"Surname: %s<br>"
"CountK: %i<br>"
"CountP: %i<br>"
"</p>",
name, surname,
atoi(countK),
atoi(countP));
char * textHTML = textToHTML(pageText);
strcat(buffer, textHTML);
free(textHTML);
}
else
{
char * pageText = "<h3>Wrong input</h3>";
char * textHTML = textToHTML(pageText);
strcat(buffer, textHTML);
free(textHTML);
}
}
socket_write_string(client, buffer);
socket_close(client);
}
