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

r囧r小猫 2022-07-14 03:47 310阅读 0赞

通过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;
    }