#include <stdio.h>
#include <sys/types.h>
#include <sys/socket.h>
#include <arpa/inet.h>
#include <unistd.h>
#include <string.h>
#include <pthread.h>
#include <sqlite3.h>
#include <errno.h>
#define ERR_MSG(msg) do{\
fprintf(stderr,"__%d__",__LINE__);\
perror(msg);\
}while(0)
#define PORT 6666//端口号
#define IP "192.168.250.100" //本机IP，ifdefine查看
//结构体类型的定义
typedef struct staff_info
{
int no;//编号
char name[20];//姓名
char passw[7];//六位密码
int type;//管理员0，员工1
int age;//年龄
char tele[9];//电话
char addr[10];//地址，如上海，南京
double salary;//工资)
}staff_info_t;
typedef struct
{
int msgtype;//请求的消息类型
int usertype;//管理员0，员工1
char username[20];//姓名
char passw[7];//登录密码
char flag;
char recvmsg[128];//通信的消息
staff_info_t sta;//员工信息
}MSG;
int newfd;
struct sockaddr_in cin;
sqlite3* db;
//函数申明
int do_accept(int sfd,pthread_t tid,sqlite3 *db);
void *callBack(void *arg);
char *errmsg=NULL;
int main(int argc, const char *argv[])
{
if(sqlite3_open("./staff.db",&db)!=SQLITE_OK)
{
printf("%s\n",sqlite3_errmsg(db));
}
else
{
printf("the staff open success\n");
}
//创建一个表格存储员工
char check[128]="select * from user where name=boss";
char **pres;
int row,column;
char sqlp_user[256]="create table if not exists user(no int,name char,passw char,type int,age int,tele char,addr char,salary double)";
if(sqlite3_exec(db,sqlp_user,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_exec:%s\n",__LINE__,errmsg);
return -1;
}
printf("create table user success\n");
if(sqlite3_get_table(db,check,&pres,&row,&column,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_get_table:%s\n",__LINE__,errmsg);
}
printf("row=%d column=%d\n",row,column);
if(0==row)
{
bzero(check,sizeof(128));
strcpy(check,"insert into user values (0,\"boss\",\"123456\",0,23,\"12345678\",\"上海\",100)");
if(sqlite3_exec(db,check,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_exec:%s\n",__LINE__,errmsg);
return -1;
}
}
printf("用户表初始化完成\n");
//创建一个表格存储历史记录
char sqlp_history[256]="create table if not exists history(no int,name char,passw char,type int,time char )";
if(sqlite3_exec(db,sqlp_history,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_exec:%s\n",__LINE__,errmsg);
return -1;
}
printf("create table history success\n");
//创建套接字
int sfd=socket(AF_INET,SOCK_STREAM,0);
if(sfd<0)
{
ERR_MSG("socket");
return -1;
}
//循序快速重建端口
int reuse=1;
if(setsockopt(sfd,SOL_SOCKET,SO_REUSEADDR,&reuse,sizeof(reuse))<0)
{
ERR_MSG("setsockopt");
return -1;
}
printf("允许端口快速重建成功\n");
pthread_t tid;
//TCP服务器模型函数
do_accept(sfd,tid,db);
//关闭数据库
if(sqlite3_close(db)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_close:%s\n",__LINE__,sqlite3_errmsg(db));
fprintf(stderr,"error_code:%d\n",sqlite3_errcode(db));
return -1;
}
printf("sqlite3 close success\n");
return 0;
}
int do_accept(int sfd,pthread_t tid,sqlite3 *db)
{
struct sockaddr_in sin;
sin.sin_family=AF_INET;
sin.sin_port=htons(PORT);//端口号的网络字节序：1024~49155
sin.sin_addr.s_addr=inet_addr(IP);//ubuntu的本机IP
//绑定服务器的IP和端口
if(bind(sfd,(struct sockaddr*)&sin,sizeof(sin))<0)
{
ERR_MSG("bind");
return -1;
}
printf("bind success\n");
//套接字设置为被动监听状态
if(listen(sfd,10)<0)
{
ERR_MSG("listen");
return -1;
}
printf("listen success\n");
//接收客户端IP和端口
struct sockaddr_in cin;
socklen_t addrlen=sizeof(cin);
//使服务器循环
while(1)
{
//获取新的文件描述符，用于通信交互
newfd=accept(sfd,(struct sockaddr*)&cin,&addrlen);
if(newfd<0)
{
ERR_MSG("accept");
return -1;
}
printf("[%s:%d] newfd=%d\n",inet_ntoa(cin.sin_addr),ntohs(cin.sin_port),newfd);
if(pthread_create(&tid,NULL,callBack,NULL)!=0)
{
ERR_MSG("pthread_create");
return -1;
}
//分离线程，之后线程会自动回收自己的资源
pthread_detach(tid);
}
//关闭套接字
close(newfd);
close(sfd);
printf("sfd close success\n");
}
//只负责交互的线程执行体
void *callBack(void *arg) //这里的arg就是主线程传的info,但是需要强转类型
{
time_t Time;
Time=time(NULL);
struct tm *t=localtime(&Time);
MSG msg;
char **pres;
int row,column;
char buf0[32]="";
char buf1[32]="";
char buf2[32]="";
char buf3[32]="";
char buf4[32]="";
char buf5[32]="";
char buf6[32]="";
char buf7[32]="";
char buf8[10]="";
char buf[128]="";//判断登录时与客户端的反馈
char arr[256]="";//姓名密码录入表中
ssize_t res=0;
while(1)
{
bzero(&msg,sizeof(msg));
//接收
res=recv(newfd,&msg,sizeof(msg),0);
if(res<0)
{
ERR_MSG("recv");
return NULL;
}
else if(0==res)
{
printf("%d 客户端关闭\n",__LINE__);
return NULL;
}
printf("[%s:%d] newfd=%d :%s :%s :%c\n",inet_ntoa(cin.sin_addr),ntohs(cin.sin_port),newfd,msg.username,msg.passw,msg.flag);
if('R'==msg.flag)//管理员登录
{
bzero(arr,sizeof(arr));
sprintf(arr,"select * from user where name= \"%s\" and passw=\"%s\" and type=%d",msg.username,msg.passw,msg.usertype);
printf("%s\n",arr);
bzero(buf,sizeof(buf));
if(sqlite3_get_table(db,arr,&pres,&row,&column,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_exec:%s\n",__LINE__,errmsg);
}
printf("row=%d\n",row);
if(0==row)
{
strcpy(buf,"no");//不能登录
printf("%s\n",buf);
}
else if(0!=row)
{
strcpy(buf,"ok");//可以登录
printf("%s\n",buf);
}
//发送
if(send(newfd,buf,sizeof(buf),0)<0)
{
ERR_MSG("send");
return NULL;
}
printf("发送成功\n");
}
else if('U'==msg.flag)//员工登录
{
bzero(arr,sizeof(arr));
sprintf(arr,"select * from user where name= \"%s\" and passw=\"%s\" and type=%d",msg.username,msg.passw,msg.usertype);
printf("%s\n",arr);
if(sqlite3_get_table(db,arr,&pres,&row,&column,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_exec:%s\n",__LINE__,errmsg);
}
printf("row=%d\n",row);
if(0==row)
{
strcpy(msg.recvmsg,"no");//不能登录
}
else if(0!=row)
{
strcpy(msg.recvmsg,"ok");//可以登录
}
if(send(newfd,&msg,sizeof(msg),0)<0)
{
ERR_MSG("send");
return NULL;
}
printf("发送成功\n");
}
else if('A'==msg.flag)//管理员添加成员
{
bzero(arr,sizeof(arr));
sprintf(arr,"insert into user values (\"%d\",\"%s\",\"%s\",\"%d\",\"%d\",\"%s\",\"%s\",\"%lf\")",msg.sta.no,msg.sta.name,msg.sta.passw,msg.sta.type,msg.sta.age,msg.sta.tele,msg.sta.addr,msg.sta.salary);
if(sqlite3_exec(db,arr,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_exec:%s\n",__LINE__,errmsg);
}
}
else if('S'==msg.flag)//管理员删除成员
{
bzero(arr,sizeof(arr));
sprintf(arr,"delete from user where name=\"%s\"",msg.username);
if(sqlite3_exec(db,arr,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_exec:%s\n",__LINE__,errmsg);
bzero(arr,sizeof(arr));
sprintf(arr,"insert into history values (\"%s\",\"%s\",\"%s\",\"%s\")",buf0,buf1,buf2,buf3);
if(sqlite3_exec(db,arr,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_exec:%s\n",__LINE__,errmsg);
}
}
}
else if('M'==msg.flag)//管理员修改成员
{
bzero(arr,sizeof(arr));
sprintf(arr,"update user set name=\"%s\",passw=\"%s\",age=\"%d\",tele=\"%s\",addr=\"%s\",salary=\"%lf\" where no= \"%d\"",msg.sta.name,msg.sta.passw,msg.sta.age,msg.sta.tele,msg.sta.addr,msg.sta.salary,msg.sta.no);
if(sqlite3_exec(db,arr,NULL,NULL,&errmsg)!=SQLITE_OK)
{
fprintf(stderr,"__%d__sqlite3_exec:%s\n",__LINE__,errmsg);
}
}
else if('F'==msg.flag)
{
bzero(arr
