Linux下的C语言编程——sqlite3实现停车场管理系统

通过C语言调用sqlite3API编程实现停车场管理系统

我自己写的很冗余，希望大家能给我一下意见

#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>

int creat_table(sqlite3 *db)
{
	char *sql;
	char *errmsg;

	int i;

	sql = "create table if not exists carpark (NO integer primary key,Num text not NULL default '',Date text not NULL default '',Code integer not NULL default '');";

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("fail: %s\n",errmsg);
		exit(-1);
	}
	

}

int insert_table(sqlite3 *db)
{
	int n;
	int i;
	char *sql;
	char *errmsg;

	printf("璇疯緭鍏ュ仠杞﹀満鎬诲叡杞︿綅鏁?\n");
	scanf("%d",&n);

	sql = "insert into carpark (NO) values (NULL);";

	for(i = 0; i < n; i++)
	{
		if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
		{
			printf("fail: %s\n",&errmsg);
			exit(-1);
		}
	}
}

int display(sqlite3 *db)
{
	int nrow;
	int ncol;
	int i;

	char *errmsg;
	char *sql;
	char **aresult;

	sql = "select * from carpark;";

	if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
	{
		printf("fail: %s\n");
		exit(-1);
	}

	for(i = 0; i < (nrow + 1) * (ncol); i++)
	{
		printf("%-20s",aresult[i]);

		if((i + 1) % (ncol) == 0)
		{
			printf("\n");
		}
	}

	sqlite3_free_table(aresult);
}

int update_table(sqlite3 *db)
{
	char sql[100];
	char *errmsg;
	char str[30];
//	char **aresult;
	char num[30];
	int flag = 1;
//	int nrow;
//	int ncol;
	int n;
	int m;
	char ch;

	time_t t;
	struct tm *tblock;
	struct timeval tv;

	while(flag)
	{
		t = time(NULL);
		m = t;
		gettimeofday(&tv,NULL);
		strftime(str,sizeof(str),"%Y/%m/%d %T",localtime(&tv.tv_sec));
	//	tblock = localtime(&t);
	//	strcpy(str,asctime(tblock));

		printf("璇疯緭鍏ヨ溅浣嶅簭鍙?\n");
		scanf("%d",&n);
		getchar();

		printf("璇疯緭鍏ヨ溅鐗屽彿!\n");
		scanf("%s",num);
		getchar();

		sprintf(sql,"update carpark set Num = '%s',Date = '%s',Code = %d where No = %d;",num,str,m,n);

		if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
		{
			printf("fail: %s\n",errmsg);
			exit(-1);
		}

		memset(sql,0,sizeof(sql));
		memset(str,0,sizeof(str));

		display(db);

		printf("continue add again?[Y/N]");
		scanf("%ch",&ch);
		getchar();

		if((ch == 'y') || (ch == 'Y'))
		{
			flag = 1;
		}
		else
		{
			flag = 0;
		}
	}

}

int solve_price(sqlite3 *db)
{
	char sql[100];
	char num[20];
	char *errmsg;
	int nrow;
	int ncol;
	int i;
	char **aresult;
	int m;
	time_t end;
	time_t begain;
	float n;
	char ch;

	int flag = 1;

	while(flag)
	{
		printf("璇疯緭鍏ヨ溅鐗屽彿:\n");
		scanf("%s",num);
		getchar();

		sprintf(sql,"select * from carpark where num = '%s';",num);

		if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
		{
			printf("fail: %s\n");
			exit(-1);
		}

		for(i = 0; i < (nrow + 1) * (ncol); i++)
		{
			printf("%-20s",aresult[i]);

			if((i + 1) % (ncol) == 0)
			{
				printf("\n");
			}
		}
		printf("\n");

		begain = atoi(aresult[2 * ncol - 1]);
//		printf("begain = %d\n",begain);
		end = time(NULL);
		n = difftime(end,begain);
		n = n / 3600;
		n = n * 10;
		m = atoi(aresult[ncol]);
//		printf("m = %d",m);

		printf("The cost is %.1f yuan \n",n);
		printf("\n");

/*	printf("input the No!\n");
	scanf("%d",&n);
	getchar();*/

		memset(sql,0,sizeof(sql));

		sprintf(sql,"update carpark set Num = '' ,Date = '',Code = '' where No = %d;",m);

		if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
		{
			printf("fail: %s\n",errmsg);
			exit(-1);
		}

		display(db);

		printf("continue?[Y/N]\n");
		scanf("%c",&ch);
		getchar();

		if((ch == 'y') || (ch == 'Y'))
		{
			flag = 1;
		}
		else
		{
			flag = 0;
		}
	}

	sqlite3_free_table(aresult);
}

int menu()
{
	int n;

	while(1)
	{
		printf("*************************************\n");
		printf("*           WELCOME                 *\n");
		printf("*	1:鍒涘缓鍋滆溅鍦?锝?2:鍋滆溅      *\n");
		printf("*       3:鍑鸿溅       锝?0:閫€鍑?     *\n");
		printf("*         4:鏌ョ湅鍋滆溅鍦虹姸鎬?         *\n");
		printf("*************************************\n");
		printf("璇疯緭鍏ユ偍鐨勯€夋嫨:\n");
		scanf("%d",&n);

		if((n == 0) || (n == 1) || (n == 2) || (n == 3) || (n == 4))
		{
			return n;
		}
		else
		{
			printf("input error! input again!\n");
		}
	}
}

int main()
{
	
	sqlite3 *db;
	
	int ret;
	int n;

	ret = sqlite3_open("park.db",&db);

	if(ret != SQLITE_OK)
	{
		perror("sqlite open:\n");
		exit(-1);
	}

	creat_table(db);

	system("clear");
	
	while(1)
	{
		n = menu();
		switch(n)
		{
			case 1:
				insert_table(db);
				display(db);
				break;
			case 2:
				system("clear");
				display(db);
				update_table(db);
				system("clear");
				break;
			case 3:
				system("clear");
				display(db);
				solve_price(db);
				system("clear");
				break;
			case 4:
                display(db);
				sleep(3);
				system("clear");
				break;
			case 0:
				printf("娆㈣繋鍐嶆浣跨敤!\n");
				exit(0);
				break;
			default:
				printf("input error!\n");
				break;
		}
	}

    return 0;
}