数据库版停车场记录-项目

客官°小女子只卖身不卖艺 2022-06-18 04:42 148阅读 0赞

**功能：显示入场时间  现在时间 时间差更新  费用实时更新  离场结算  停车位置剩余数 **

#include <stdio.h>
    #include<sqlite3.h>
    #include<stdlib.h>
    #include<errno.h>
    #include<time.h>
    #include<string.h>
    
    void create_table(sqlite3 *db)//创建表
    {
    	int ret;	
    	char *sql=NULL;
    	char *error=NULL;
    	sql = "create table if not exists mytable(ID integer primary key,车牌号 text,姓名 text,停车时间 text,现在时间 text);";
    	ret = sqlite3_exec(db,sql,NULL,NULL,&error);
    
    	if(ret != SQLITE_OK)
    	{
    		printf("create table error:%s\n",error);
    		perror("create table fail");
    		exit(0);
    	}
    }
    
    void look_park(sqlite3 *db)//新建停车场
    {
    	int n;
    	int i;
    	int ret;
    
    	char * sql = NULL;
    	char * errmsg = NULL;
    	sql = "insert into mytable (id) values (NULL);";
    	for(i = 0; i < 15; i++)
    	{
    		ret = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
    		if(ret)
    		{
    
    			printf("insert fail:%s\n", &errmsg);
    			exit(-1);
    		}
    	}
    }
    
    
    void insert_values(sqlite3 *db,char *name,char *id)//插入值
    {
    
    	int ret,Ret;
    	char sql[300];
    	char *Sql=NULL;
    	char *errmsg=NULL;
    	int n_col,N_row,n_row;
    	char **presult;
    	char *error=NULL;
    	char Time[200];
    	int i=0,k=0;
    	int m=0,n=0,sum=0;
    	time_t t = time(NULL); 
    	struct tmzone * tz;
    	struct timeval tv;
    	char tmp[20]; 
    
    	strftime( tmp, sizeof(tmp), "%X",localtime(&t) ); 
    	Sql="select * from mytable;";
    	ret = sqlite3_get_table(db, Sql, &presult, &N_row, &n_col, &error);
    	if(ret != SQLITE_OK)
    	{
    		printf("select 1 fail:%s\n", &error);
    		exit(-1);
    	}
    
    	while(m < N_row+1)
    	{
    		m++;
    		sprintf(sql, "select * from mytable where ID = %d;", m);
    		ret = sqlite3_get_table(db,sql,&presult,&n_row,&n_col,&errmsg);
    		if(SQLITE_OK != ret)
    		{
    			printf("get table fail:%s\n",errmsg);
    			exit(-1);
    		}
    		if(presult[2*n_col - 5] == NULL)break;
    	}
    	sprintf(sql, "update mytable set 车牌号 = '%s', 姓名 = '%s',停车时间 = '%s',现在时间 = '%s' where ID = %d;",id,name,tmp,tmp,m);
    
    
    	ret = sqlite3_exec(db,sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("Insert values fail:%s\n",error);
    		exit(0);
    	}
    	if(ret == SQLITE_OK)
    	{
    		printf("登记成功！\n");
    	}
    	sqlite3_free_table(presult);
    
    }
    
    void inquire_nocb2(sqlite3 *db)//查看表
    {
    
    	char *sql = NULL;
    	char **presult = NULL;
    	char *errmsg;
    	int n_row,n_col;
    	int ret;
    	int i;
    
    	sql = "select * from mytable;";
    	ret = sqlite3_get_table(db,sql,&presult,&n_row,&n_col,&errmsg);
    	if(SQLITE_OK != ret)
    	{
    
    		printf("get table fail:%s\n",errmsg);
    		exit(-1);
    	}
    	for(i = 0;i < (n_row + 1)*n_col;i++)
    	{
    		if(i<=4)
    		{
    			printf("%-16s",presult[i]);
    			if(i==4)
    			{
    				printf("\n");
    				printf("______________________________________________________________________\n");
    			}
    		}
    		else
    		{
    			printf("%-13s",presult[i]);
    			if((i+1)%n_col == 0)
    			{
    				printf("\n");
    			}
    		}
    	}
    	sqlite3_free_table(presult);
    }
    void inquire_nocb1(sqlite3 *db)//查看表
    {
    
    	char *sql = NULL;
    	char **presult = NULL;
    	char *errmsg;
    	int n_row,n_col;
    	int ret;
    	int i;
    
    	sql = "select * from mytable;";
    	ret = sqlite3_get_table(db,sql,&presult,&n_row,&n_col,&errmsg);
    	if(SQLITE_OK != ret)
    	{
    
    		printf("get table fail:%s\n",errmsg);
    		exit(-1);
    	}
    	for(i = 0;i < (n_row + 1)*n_col;i++)
    	{
    		if(i<=6)
    		{
    			printf("%-16s",presult[i]);
    			if(i==6)
    			{
    				printf("\n");
    				printf("_______________________________________________________________________________\n");
    			}
    		}
    		else
    		{
    			printf("%-13s",presult[i]);
    			if((i+1)%n_col == 0)
    			{
    				printf("\n");
    			}
    		}
    	}
    	sqlite3_free_table(presult);
    }
    void look_name(sqlite3 *db,char *name)//以输入查询单个信息 
    {
    
    	char sql[100];  
    	char **presult = NULL;  
    	char *errmsg;  
    	int n_row,n_col;  
    	int ret;  
    	int i;  
    
    	sprintf(sql,"select * from mytable where 姓名 = '%s';",name);  
    	ret = sqlite3_get_table(db,sql,&presult,&n_row,&n_col,&errmsg);  
    	if(SQLITE_OK != ret)  
    	{
    
    		printf("get table fail:%s\n",errmsg);  
    		exit(-1);  
    	}  
    	printf("车辆信息为:\n");  
    
    	for(i = 0;i < (n_row + 1)*n_col;i++)  
    	{
    		if(i<=6)  
    		{
    			printf("%-16s",presult[i]);  
    			if(i==6)
    			{
    				printf("\n");
    				printf("_______________________________________________________________________________\n");
    			}
    		}  
    		else  
    		{
    			printf("%-13s",presult[i]);  
    
    			if((i+1)%n_col == 0)  
    				printf("\n");  
    			if((i+1) == n_col)  
    
    				printf("_______________________________________________________________________________\n");
    		}  
    	}  
    	sqlite3_free_table(presult);  
    } 
    
    void look_list(sqlite3 *db,char *name,int k)//选择输入信息查询信息  
    {
    
    	char sql[100];  
    	char **presult = NULL;  
    	char *errmsg;  
    	int n_row,n_col;  
    	int ret;  
    	int i;  
    	if(k==1)
    	{
    		sprintf(sql,"select * from mytable where 车牌号 like '%s';",name);  
    	}
    	else if(k==2)
    	{
    		sprintf(sql,"select * from mytable where 姓名 like '%s';",name);  
    	}
    	else
    	{
    
    		sprintf(sql,"select * from mytable where 车牌号 like '%s';",name);  
    	}
    	ret = sqlite3_get_table(db,sql,&presult,&n_row,&n_col,&errmsg);  
    	if(SQLITE_OK != ret)  
    	{
    		printf("get table fail:%s\n",errmsg);  
    		exit(-1);  
    	}  
    	printf("车辆信息为:\n");  
    
    	for(i = 0;i < (n_row + 1)*n_col;i++)  
    	{
    		if(i<=6)  
    		{
    			printf("%-16s",presult[i]);  
    			if(i==6)
    			{
    				printf("\n");
    				printf("________________________________________________\n");
    			}
    		}  
    		else  
    		{
    			printf("%-13s",presult[i]);  
    			if((i+1)%n_col == 0)  
    				printf("\n");  
    			if((i+1) == n_col)  
    				printf("_______________________________________________________________________________\n");
    		}  
    	}  
    	sqlite3_free_table(presult);  
    } 
    void update_time(sqlite3 *db)//更新现在时间
    {
    	int ret;
    	char *Sql=NULL;
    	char sql[100];
    	char *error=NULL;
    	int i;
    	char **presult=NULL;
    	int n_row,n_col;
    	time_t t = time(0); 
    	char tmp[20]; 
    	char Tmp[20];
    	int j,k,n=0;
    	strftime( tmp, sizeof(tmp), "%X",localtime(&t) );
    	k=strlen(tmp)-1;
    	Sql ="create table newtable as select ID,车牌号,姓名,停车时间 from mytable;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("newtable 2 select fail:%s\n",error);
    		exit(0);
    	}
    	Sql ="drop table mytable;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("drop table  fail:%s\n",error);
    		exit(0);
    	}
    	Sql ="alter table newtable rename to mytable;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("rename table  fail:%s\n",error);
    		exit(0);
    	}
    	sprintf(sql,"alter table mytable add column 现在时间 not null default '%s';",tmp);
    	ret = sqlite3_exec(db,sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("add colmun time fail:%s\n",error);
    		exit(0);
    	}
    }
    void sub_time(sqlite3 *db)//增加一列停车时数
    {
    	int ret;
    	char *Sql=NULL;
    	char sql[100];
    	char *error=NULL;
    	int i;
    	char **presult=NULL;
    	int n_row,n_col;
    	time_t t = time(0); 
    	char tmp[20]; 
    	char Tmp[20];
    	int j,k,n=0;
    	strftime( tmp, sizeof(tmp), "%X",localtime(&t) );
    	k=strlen(tmp)-1;
    	Sql ="create table newtable as select ID,车牌号,姓名,停车时间,现在时间 from mytable;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("newtable 1 select fail:%s\n",error);
    		exit(0);
    	}
    	Sql ="drop table mytable;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("drop table  fail:%s\n",error);
    		exit(0);
    	}
    	Sql ="alter table newtable rename to mytable;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("rename table  fail:%s\n",error);
    		exit(0);
    	}
    	Sql="alter table mytable add column 停车时数 integer;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("alter colmun time  fail:%s\n",error);
    		exit(0);
    	}
    
    }
    void dol(sqlite3 *db)//增加一列费用
    {
    	int ret;
    	char *Sql=NULL;
    	char sql[100];
    	char *error=NULL;
    	int i;
    	char **presult=NULL;
    	int n_row,n_col;
    	time_t t = time(0); 
    	char tmp[20]; 
    	char Tmp[20];
    	int j,k,n=0;
    	strftime( tmp, sizeof(tmp), "%X",localtime(&t) );
    	k=strlen(tmp)-1;
    	Sql ="create table newtable as select ID,车牌号,姓名,停车时间,现在时间,停车时数 from mytable;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("newtable 1 select fail:%s\n",error);
    		exit(0);
    	}
    	Sql ="drop table mytable;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("drop table  fail:%s\n",error);
    		exit(0);
    	}
    	Sql ="alter table newtable rename to mytable;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("rename table  fail:%s\n",error);
    		exit(0);
    	}
    
    	Sql="alter table mytable add column 费用 integer;";
    	ret = sqlite3_exec(db,Sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("alter colmun dol  fail:%s\n",error);
    		exit(0);
    	}
    
    }
    void sum_time(sqlite3 *db)//计算时间查和费用
    {
    
    	int ret;
    	char *Sql=NULL;
    	char sql[100];
    	char *error=NULL;
    	int i=0,M;
    	char **presult=NULL;
    	int n_row,n_col,N_row;
    	char Tmp[20];
    	int j,k,n=0,id;
    	float end;
    	double d_value=0;
    	double hour=0;
    	char end_min[20];
    	int end_m=0;
    	int end_h=0;
    	int start_m=0;
    	int start_h=0;
    	char start_min[20];
    	int o,y;
    	float dol;
    	unsigned m,e;
    	char tmp[20]; 
    	float min;
    
    	memset(sql, 0, sizeof(sql));
    	Sql="select * from mytable;";
    	ret = sqlite3_get_table(db, Sql, &presult, &N_row, &n_col, &error);
    	if(ret != SQLITE_OK)
    	{
    		printf("select 1 fail:%s\n", &error);
    		exit(-1);
    
    	}
    	sqlite3_free_table(presult);	
    	while(i<N_row)
    	{
    		i++;
    		sprintf(sql, "select * from mytable where ID = %d;", i);
    		ret = sqlite3_get_table(db, sql, &presult, &n_row, &n_col, &error);
    		if(ret != SQLITE_OK)
    		{
    			printf("select 2 fail:%s\n", &error);
    			exit(-1);
    		}
    		if(presult[2*n_col - 5] != NULL)
    		{
    			memset(start_min, 0, sizeof(start_min));
    			memset(end_min, 0, sizeof(end_min));
    			end_h = atoi(presult[2*n_col - 3]);//结束小时int
    			start_h = atoi(presult[2*n_col - 4]);//开始小时int
    			for(o=0;o<5;o++)
    			{
    				end_min[o]=presult[2*n_col - 3][o+3];//分钟char
    			}
    			end_min[3]='\0';
    			end_m = atoi(end_min);
    
    			for(o=0;o<5;o++)
    			{
    				start_min[o]=presult[2*n_col - 4][o+3];//分钟char
    			}
    			start_min[3]='\0';
    			start_m = atoi(start_min);
    			//printf("start:%d:%d\n",start_h,start_m); 
    			//printf("end:%d:%d\n",end_h,end_m); 
    
    			if(end_h >= start_h && (end_m < start_m))
    			{
    				min = (60 - start_m + end_m)%60*10;
    				min = 0.001*min;
    				end = end_h - start_h;
    				end = end + min;
    			}
    			else if(end_h >= start_h && (end_m > start_m))
    			{
    				min = (end_m - start_m)%60*10;
    				min = 0.001*min;
    				end = end_h - start_h;
    				end = end + min;
    			}
    			else if(end_h < start_h && (end_m > start_m))
    			{
    				min = (end_m - start_m)%60 * 10;
    				min = 0.001*min;
    				end = end_h + 24 - start_h;
    				end = end + min;
    			}
    			if(end_h < start_h && (end_m < start_m))
    			{
    				min = (60 - start_m + end_m)%60 * 10;
    				min = 0.001*min;
    				end = end_h + 24 - start_h;
    				end = end + min;
    			}
    			dol = 3*end;
    			sprintf(sql,"update mytable set 停车时数 = %0.01f where ID = %d;",end,i);
    			ret = sqlite3_exec(db,sql,NULL,NULL,&error);
    			if(ret != SQLITE_OK)
    			{
    				printf("update time fail:%s",error);
    				exit(-1);
    			}
    			sprintf(sql,"update mytable set 费用 = %0.01f where ID = %d;",dol,i);
    			ret = sqlite3_exec(db,sql,NULL,NULL,&error);
    			if(ret != SQLITE_OK)
    			{
    				printf("update dol fail:%s",error);
    				exit(-1);
    			}
    		}
    
    	}
    	sqlite3_free_table(presult);
    }
    void out_park(sqlite3 *db,char *id)//离开赋值NULL
    {
    	char *Sql=NULL;
    	int ret;
    	char sql[100];
    	char *error=NULL;
    	int n_col,n_row;
    	char **presult =NULL;
    	int ID;
    
    	sprintf(sql, "select * from mytable where 车牌号 = '%s';", id);
    	ret = sqlite3_get_table(db, sql, &presult, &n_row, &n_col, &error);
    	ID = atoi(presult[n_col]);
    	memset(sql, 0, sizeof(sql));
    	sprintf(sql, "update mytable set 车牌号 = NULL,姓名 = NULL,停车时间 = NULL,现在时间 = NULL where ID = %d;",ID);
    
    	ret = sqlite3_exec(db,sql,NULL,NULL,&error);
    
    	if(ret != SQLITE_OK)
    	{
    		printf("Insert values fail:%s\n",error);
    		exit(0);
    	}
    	if(ret == SQLITE_OK)
    	{
    		printf("离开成功！\n");
    	}
    	sqlite3_free_table(presult);
    }
    int park_num(sqlite3 *db)//计算停车剩余数
    {
    
    	int ret,Ret;
    	char sql[300];
    	char *Sql=NULL;
    	char *errmsg=NULL;
    	int n_col,N_row,n_row;
    	char **presult;
    	char *error=NULL;
    	char Time[200];
    	int i=0,k=0;
    	int m=0,n=0,sum=0;
    	time_t t = time(NULL); 
    	struct tmzone * tz;
    	struct timeval tv;
    	char tmp[20]; 
    	Sql="select * from mytable;";
    	ret = sqlite3_get_table(db, Sql, &presult, &N_row, &n_col, &error);
    	if(ret != SQLITE_OK)
    	{
    		printf("select 1 fail:%s\n", &error);
    		exit(-1);
    	}
    	while(n < N_row+1)
    	{
    		n++;
    		sprintf(sql, "select * from mytable where ID = %d;", n);
    		ret = sqlite3_get_table(db,sql,&presult,&n_row,&n_col,&errmsg);
    		if(SQLITE_OK != ret)
    		{
    			printf("get table fail:%s\n",errmsg);
    			exit(-1);
    		}
    		if(presult[n_col + 1] == NULL)
    		{
    			sum++;
    			if(n>=N_row)break;
    		}
    	}
    	return sum;
    }
    void droptable(sqlite3 *db)//删除表
    {
    	char *sql=NULL;
    	char *error=NULL;
    	int ret;
    
    	sql ="drop table mytable;";
    	ret = sqlite3_exec(db,sql,NULL,NULL,&error);
    	if(ret != SQLITE_OK)
    	{
    		printf("drop table 100 fail:%s\n",error);
    		exit(0);
    	}
    }
    void push(sqlite3 *db)//刷新时间函数组
    {
    
    				    update_time(db);
    					sub_time(db);
    					dol(db);
    					sum_time(db);
    }
    int main()
    {
    	sqlite3 *db;
    	int ret,k;
    	char id[100];
    	int n,y;
    	char name[100];
    	int i,q=0;
    	char old_time;
        int sum;
     
    	ret=sqlite3_open("mydatabase.db",&db);
    	if(ret != SQLITE_OK)
    	{
    		printf("Create my database error!\n");
    		exit(0);
    	}
    	printf("successfull database!\n ");
    
    	create_table(db);
    	sum = park_num(db);
    	while(1)
    	{
    		printf("***************************************** \n");
    		printf("            [停车系统菜单]                \n");
    		printf("_________________________________________ \n");
    		printf("    [1]登记车辆        [2]查询车辆        \n");
    		printf("    [3]结算时间        [4]查看清单/刷新   \n");
    		printf("    [5]退出系统        [100]新建停车                   \n");
    		printf("              剩余停车位:%d               \n",sum);
    		printf("_________________________________________ \n");
    		printf("请选择功能项:");
    		scanf("%d",&n);
    		switch(n)
    		{
    			case 1:
    				{   
                        push(db);
    					printf("请输入车牌号:");
    					scanf("%s",id);
    					printf("请输入姓名:");
    					scanf("%s",name);
    					insert_values(db,name,id);
    					look_name(db,name);  
    					printf("           **按回车返回主菜单**           \n");
    					getchar();getchar();
    					break;
    				}
    			case 2:
    				{
                        push(db);
    					printf("[1]输入车牌号    [2]输入姓名\n");
    					printf("请选择以什么方式:");
    					scanf("%d",&k);
    					if(k==1)
    					{
    						printf("请输入车牌号:");
    						scanf("%s",id);
    						look_list(db,id,k);  
    						break;
    					}
    					else if(k==2)
    					{
    
    						printf("请输入姓名:");
    						scanf("%s",name);
    						look_list(db,name,k);  
    						break;
    					}
    					else
    					{
    						printf("输入错误!\n");
    						break;
    					}
    				}
    			case 3:
    				{
    						printf("请输入车牌号:");
    						scanf("%s",id);
                        push(db);
    					look_list(db,id,k);  
    					printf("是否确认离开(0/1)\n");
    					scanf("%d",&y);
    					if(y==1)
    					out_park(db,id);
    					break;
    				}
    			case 4:
    				{
    
    				
                        push(db);
    					inquire_nocb1(db);
    					break;
    				}
    			case 100:
    				{
                        droptable(db);
    	create_table(db);
    					look_park(db);
    					inquire_nocb2(db);
    					break;
    				}
    			case 5:
    				{
    
    					sqlite3_close(db);
    					exit(0);
    					break;
    				}
    			case 6:{
    					inquire_nocb1(db);
    
    				   }
    		}
    	sum = park_num(db);
    
    	}
    	return 0;
    }

**  **