基于Linux C编程+TCP/IP协议、多线程的方式+SQLite3数据库实现的员工管理系统+项目源码+文档说明

/************************************************************************* > File Name: operationFunctions.c > Author: Hippyll > Mail: hippyll@foxmail.com > Created Time: 2021年08月06日 星期五 21时49分56秒 ************************************************************************/ #include "../include/smsServer.h" /* int insertNewUser(sqlite3 *pdb,char *username,char *password); int addNewStaffInfo(sqlite3 *pdb); int deleteStaffByName(sqlite3 *pdb,char *staffname); int modifyUserPassword(sqlite3 *pdb,char *username,char *password); int modifyStaffInfo(sqlite3 *pdb,char *staffname); int printUserTable(sqlite3 *pdb); int printStaffInfoTable(sqlite3 *pdb); struct acceptStaffInfo *printStaffInfoTableByName(sqlite3 *pdb,char *staffname); int printHistoryTableByName(sqlite3 *pdb,char *staffname); int insertHistoryRecord(sqlite3 *pdb,char *staffname,char *operation); int checkLogin(sqlite3 *pdb,char *username,char *password); int updateUserFlag(sqlite3 *pdb,char *username); */ static int inputStaffInfo(struct staffInfo *staff); /*向user表中添加新记录*/ int insertNewUser(sqlite3 *pdb,char *username,char *password) { int ret = 0; char sqlstr[1024] = ""; char **ppret = NULL; int rows = 0,cols = 0; char *perr = NULL; /*比较姓名*/ sprintf(sqlstr,"SELECT * FROM user WHERE name LIKE '%s'",username); ret = my_sqlite3_get_table(pdb,sqlstr,&ppret,&rows,&cols,&perr); if(ret != SQLITE_OK){ PrintServerError("get_table %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; sqlite3_close(pdb); pdb = NULL; return -1; }else if(strcmp(*(ppret+3),username) == 0){ /*该用户已存在*/ // printf("the username %s is exist!\n",username); return 0; }else{ /*向user表中添加信息*/ memset(sqlstr,0,1024); sprintf(sqlstr,"INSERT INTO user(Name,Password,Flag) VALUES('%s','%s',0)",username,password); ret = my_sqlite3_exec(pdb,sqlstr,NULL,NULL,&perr); if(ret != SQLITE_OK){ PrintServerError("exec %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } } return 0; } /*向staffInfo表中添加新记录*/ int addNewStaffInfo(sqlite3 *pdb) { int ret = 0; char sqlstr[1024] = ""; char *perr = NULL; struct staffInfo *staff = NULL; staff = (struct staffInfo *)malloc(sizeof(struct staffInfo)); memset(staff,0,sizeof(struct staffInfo)); /*向staffInfo表中插入数据*/ inputStaffInfo(staff); memset(sqlstr,0,1024); sprintf(sqlstr,"INSERT INTO staffInfo(Name,Address,Age,level,ID,Salary,Phone,Email) VALUES('%s','%s','%d','%d','%d','%f','%s','%s')", staff->name,staff->address,staff->age,staff->level,staff->id,staff->salary,staff->phone,staff->email); ret = my_sqlite3_exec(pdb,sqlstr,NULL,NULL,&perr); if(ret != SQLITE_OK){ PrintServerError("exec %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } /*同时在user表中添加相关信息*/ memset(sqlstr,0,1024); sprintf(sqlstr,"INSERT INTO user(Name,Password,Flag) VALUES('%s','%s',0)",staff->name,STAFF_INITIAL_PASSWD); ret = my_sqlite3_exec(pdb,sqlstr,NULL,NULL,&perr); if(ret != SQLITE_OK){ PrintServerError("exec %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } return 0; } /*通过名字删除成员信息*/ int deleteStaffByName(sqlite3 *pdb,char *staffname) { int ret = 0; char sqlstr[1024] = ""; char **ppret = NULL; int rows = 0,cols = 0; char *perr = NULL; memset(sqlstr,0,1024); sprintf(sqlstr,"SELECT count(*) FROM user"); ret = my_sqlite3_get_table(pdb,sqlstr,&ppret,&rows,&cols,&perr); if(ret != SQLITE_OK){ PrintServerError("my_sqlite3_get_table %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } sscanf(*(ppret+1),"%d",&ret); if(0 == ret){ printf("The user Name:%s has no record in Tables!\n",staffname); return -1; }else{ /*删除user表中的信息*/ memset(sqlstr,0,1024); sprintf(sqlstr,"DELETE FROM user WHERE name='%s'",staffname); ret = my_sqlite3_exec(pdb,sqlstr,NULL,NULL,&perr); if(ret != SQLITE_OK){ PrintServerError("exec %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } /*删除staffInfo表中的信息*/ memset(sqlstr,0,1024); sprintf(sqlstr,"DELETE FROM staffInfo WHERE name='%s'",staffname); ret = my_sqlite3_exec(pdb,sqlstr,NULL,NULL,&perr); if(ret != SQLITE_OK){ PrintServerError("exec %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } } return 0; } /*更改user表的登入密码*/ int modifyUserPassword(sqlite3 *pdb,char *username,char *password) { int ret = 0; char sqlstr[1024] = ""; char operation[OPERATION_LENGTH] = "Modify password"; char **ppret = NULL; int rows = 0,cols = 0; char *perr = NULL; memset(sqlstr,0,1024); sprintf(sqlstr,"SELECT count(*) FROM user WHERE name='%s'",username); ret = my_sqlite3_get_table(pdb,sqlstr,&ppret,&rows,&cols,&perr); if(ret != SQLITE_OK){ PrintServerError("my_sqlite3_get_table %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } sscanf(*(ppret+1),"%d",&ret); if(0 == ret){ printf("The user Name:%s has no record in Tables!\n",username); return -1; }else{ memset(sqlstr,0,1024); sprintf(sqlstr,"UPDATE user SET Password='%s' WHERE Name='%s'",password,username); ret = my_sqlite3_exec(pdb,sqlstr,NULL,NULL,&perr); if(ret != SQLITE_OK){ PrintServerError("exec %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } ret = insertHistoryRecord(pdb,username,operation); if(ret != 0){ PrintServerError("Insert history records failed\n"); return -1; } } return 0; } /*更改staffInfo表中的信息*/ int modifyStaffInfo(sqlite3 *pdb,char *staffname) { int ret = 0; char sqlstr[1024] = ""; char **ppret = NULL; int rows = 0,cols = 0; char *perr = NULL; char column[NORMAL_LENGTH] = ""; int i = 0; memset(sqlstr,0,1024); sprintf(sqlstr,"SELECT count(*) FROM StaffInfo WHERE Name='%s'",staffname); ret = my_sqlite3_get_table(pdb,sqlstr,&ppret,&rows,&cols,&perr); if(ret != SQLITE_OK){ PrintServerError("my_sqlite3_get_table %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } sscanf(*(ppret+1),"%d",&ret); if(0 == ret){ printf("The user Name:%s has no record in the Tables!\n",staffname); return -1; }else{ printf("please input the column which want to modify:"); myGetString(column,NORMAL_LENGTH); /* *判断选择的column是字符串型还是整型或浮点型 */ if(strncasecmp(column,"name",(int)sizeof("name")) == 0 ||strncasecmp(column,"address",(int)sizeof("address")) == 0 || strncasecmp(column,"phone",(int)sizeof("phone")) == 0 ||strncasecmp(column,"email",(int)sizeof("email")) == 0){ char value[NORMAL_LENGTH] = ""; //字符串型数据，用char[]接收 printf("please input the value which want to modify:"); myGetString(value,NORMAL_LENGTH); memset(sqlstr,0,1024); sprintf(sqlstr,"UPDATE staffInfo SET %s='%s' WHERE Name='%s'",column,value,staffname); ret = my_sqlite3_exec(pdb,sqlstr,NULL,NULL,&perr); if(ret != SQLITE_OK){ PrintServerError("exec %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } }else if(strncasecmp(column,"age",(int)sizeof("age")) == 0 ||strncasecmp(column,"level",(int)sizeof("level")) == 0 || strncasecmp(column,"id",(int)sizeof("id")) == 0){ int val = 0; //整型数据，用int接收 printf("please input the value which want to modify:"); scanf("%d",&val); getchar(); memset(sqlstr,0,1024); sprintf(sqlstr,"UPDATE staffInfo SET %s=%d WHERE Name='%s'",column,val,staffname); ret = my_sqlite3_exec(pdb,sqlstr,NULL,NULL,&perr); if(ret != SQLITE_OK){ PrintServerError("exec %s failed:%s\n",sqlstr,perr); sqlite3_free(perr); perr = NULL; return -1; } }else if(strncasecmp(column,"salary",(int)sizeof("salary")) == 0){ float val = 0.0; //浮点型数据，用float接收 pri