#include<myhead.h>

int do_add(sqlite3* ppDb)
{
	int numb;
	char name[10];
	int salary;
	printf("请输入员工的信息：");
	scanf("%d %s %d",&numb, name, &salary);
	//将员工的信息存储到数组
	char sql_add[128] = "";
	sprintf(sql_add,"INSERT INTO Worker values(%d,'%s',%d)",numb,name,salary);
	char *errmsg = NULL;   //存放执行sql语句的错误信息
	if(sqlite3_exec(ppDb, sql_add, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error: %s\n", errmsg);
		sqlite3_free(errmsg);
		errmsg = NULL;
		return -1;
	}

}

int do_delete(sqlite3* ppDb)
{
	//定义一个数组存储sql语句
	char sql[128]="";
	char *errmsg = NULL;
	int numb;
	printf("请输入要删除的员工的工号：");
	scanf("%d",&numb);
	sprintf(sql,"delete from Worker where numb =%d",numb);
	if(sqlite3_exec(ppDb, sql, NULL,NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error:%s\n",errmsg);
		errmsg = NULL;
		return -1;
	}
}

int do_update(sqlite3* ppDb)
{
	//将员工的信息存储到数组
	char sql[128] = "";
	int salary;
	printf("请输入要更新的员工的信息:");
	scanf("%d",&salary);
	sprintf(sql,"UPDATE Worker SET salary=%d where id=1001",salary);
	char *errmsg = NULL;   //存放执行sql语句的错误信息
	if(sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error: %s\n", errmsg);
		sqlite3_free(errmsg);
		errmsg = NULL;
		return -1;
	}

}
int do_search(sqlite3* ppDb)
{

	//将员工的信息存储到数组
	char sql_add[128] = "";
	printf("请输入要查询的员工的信息:");
	gets(sql_add);
	char *errmsg = NULL;   //存放执行sql语句的错误信息
	if(sqlite3_exec(ppDb, sql_add, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error: %s\n", errmsg);
		sqlite3_free(errmsg);
		errmsg = NULL;
		return -1;
	}

}




int main(int argc, const char *argv[])
{
	//定义数据库句柄指针
	sqlite3 * ppDb = NULL;
	//打开数据库，如果数据库不存在，则创建数据库
	//将数据库句柄由参数2返回
	if(sqlite3_open("./my.db", &ppDb) != SQLITE_OK)
	{
		printf("sqlite3_open error,errcode=%d, errmsg = %s\n",\
				sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb));
		return -1;
	}
	printf("sqlite3_open success\n");

	//创建数据表
	//1、准备sql语句
	char sql[128] = "create table if not exists Worker(numb int, name char, salary double)";
	//char *sql = "create table if not exists Worker(numb int, name char, salary double)";
	char *errmsg = NULL;       //存放执行sql语句口的错误信息
	if(sqlite3_exec(ppDb, sql, NULL, NULL, &errmsg) != SQLITE_OK)
	{
		printf("exec error : %s\n", errmsg);
		sqlite3_free(errmsg);             //防止内存泄露
		errmsg = NULL;
		return -1;
	}
	printf("员工信息表创建成功\n");

	int menu = -1;         //菜单选项
	for(;;)
	{
		system("clear");             //清空之前的终端信息
		printf("\t\t=======XXX公司员工管理系统=======\n");
		printf("\t\t======1、添加员工信息=========\n");
		printf("\t\t======2、删除员工信息=========\n");
		printf("\t\t======3、修改员工信息=========\n");
		printf("\t\t======4、查询员工信息=========\n");
		printf("\t\t======0、退出=========\n");

		printf("请输入操作码:");          //提示并输入信息
		scanf("%d", &menu);
		getchar();                          //吸收回车

		switch(menu)
		{
		case 1:
			{
				do_add(ppDb);
			}
			break;
		case 2:
			{
				do_delete(ppDb);
			}
			break;
		case 3:
			{
				do_update(ppDb);
			}
			break;
		case 4:
			{
				do_search(ppDb);
			}
			break;
		case 0:
			goto END;
		default:printf("您输入的功能有误，请重新输入！！！\n");
		}

		printf("请输入任意键，按回车清屏\n");
		while(getchar() != '\n');

	}




END:


	//关闭数据库
	sqlite3_close(ppDb);


	return 0;
}