sqlite3数据库之停车场

#include<stdio.h>
#include<stdlib.h>
#include<sqlite3.h>
#include<error.h>
#include<unistd.h>
#include<time.h>

#define NUM_CARPORT 10
#define PRICE 5.0    //每小时的价格


void table_error(int ret,char * str,char * errmsg)
{
	if(ret == -1)
	{
		printf("%s fail:%s\n",str,errmsg);
		exit(-1);
	}
}

void create_table(sqlite3 * db)
{
	int ret;
	char * errmsg;
	char * sql;
	sql = "create table if not exists mytable(carport integer primary key,carno text,intime text,outtime text,money text,state text,temp integer);";
	ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	table_error(ret,"create",errmsg);
}

char regular(sqlite3 * db)
{
	int i;
	int ret;
	char **presult;
	int row,col;
	char *errmsg;
	char * sql;

	sql = "select * from mytable;";
	ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);
	if(row >= NUM_CARPORT)
		return 'N';
}

void make_port(sqlite3 * db)
{
	int ret;
	int i;
	char c;
	char * sql;
	char * errmsg;

    c = regular(db);
	if(c == 'N')
		return;

	for(i = 0; i < NUM_CARPORT;i++)
	{
		sql = "insert into mytable(carport,carno,intime,outtime,money,state) values(NULL,'','','','','YES');";
		ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
		table_error(ret,"update",errmsg);
	}

}

int show()
{
	int num;
	printf("\
               ╔═════════════════╦═════════════════╗\n\
               ║          欢迎使用停车场！         ║\n\
               ║1.停车           │2.取车收费       ║\n\
               ║3.查看车位状态   │4.查看停车记录   ║\n\
               ║5.退出           │                 ║\n\
               ╚═════════════════╩═════════════════╝\n");
	printf("请选择需要的功能选项：\n");
	scanf("%d",&num);
	getchar();
	if(num < 1 || num > 5)
	{
		printf("没有该选项！请重新输入！\n");
		show();
	}
	else
		return num;
}

void  in_port(sqlite3 * db)
{ 
	int time1;
	time_t t3;
	struct tm *tm;
	int n;
	int i;
	int ret;
	int row,col;
	char * errmsg;
	char ** presult;
	char * sql;
	char sql3[100];
	char carno[100];
	char sql2[100];

	time1 = time(&t3);
	tm = localtime(&t3);

	printf("请输入停车车牌:\n");
	scanf("%s",carno);
	printf("当前空闲车位如下:\n");

	sql = "select carport,carno,intime,outtime,money,state from mytable where state = 'YES';";
	ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);
	table_error(ret,"获取空闲车位",errmsg);
	for(i = 0;i < (row + 1) * col;i++)
	{
		printf("|%-8s|",presult[i]);
		if((i + 1) % col == 0)
			printf("\n");
	}
	printf("请选择要停的车位:\n");
	scanf("%d",&n);
	getchar();
	if(n < 0 || n > NUM_CARPORT)
	{
		printf("没有此车位！\n");
        in_port(db);
	}
	else
	{
		sprintf(sql2,"update mytable set carno ='%s',intime = '%d-%d-%d %d:%d:%d ',state = 'NO' where carport = %d;",carno,tm -> tm_year + 1900,tm-> tm_mon + 1,tm->tm_mday,tm->tm_hour,tm->tm_min,tm->tm_sec,n);
		ret = sqlite3_exec(db,sql2,NULL,NULL,&errmsg);
		if(ret == -1)
		{
			printf("停车失败:%s\n",errmsg);
		}
		else
		{
			printf("停车成功!\n");
		}
//		printf("%d\n",time1);
		sprintf(sql2,"update mytable set temp = %d where carno = '%s';",time1,carno);
		ret = sqlite3_exec(db,sql2,NULL,NULL,&errmsg);
		table_error(ret,"temp",errmsg);

		sprintf(sql3,"insert into mytable(carport,carno,intime,outtime,money,state) values(NULL,'%s','%d-%d-%d %d:%d:%d','还在停车场            ','','');",carno,tm -> tm_year + 1900,tm-> tm_mon + 1,tm->tm_mday,tm->tm_hour,tm->tm_min,tm->tm_sec);
	//	sql3 = "insert into mytable(carport,carno,intime,outtime,money,state) values(NULL,'','','','','');";
		ret = sqlite3_exec(db,sql3,NULL,NULL,&errmsg);
		if(ret == -1)
		{
			printf("备份失败:%s\n",errmsg);
		}


	}
	


}

void look_port(sqlite3 * db)
{
	int i,j;
	int ret;
	char **presult;
	int row,col;
	char *errmsg;
	char sql[100];

	sprintf(sql,"select carport,carno,intime,outtime,money,state from mytable where carport <= %d",NUM_CARPORT);
//	sprintf(sql,"select * from mytable where carport <= %d",NUM_CARPORT);
	ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);
	table_error(ret,"Look",errmsg);

	for(i = 0;i < (row +1)*col;)
	{
	
		printf("|%-10s|",presult[i]);
		i++;
		printf("|%-12s|",presult[i]);
		i++;
		printf("|%-22s|",presult[i]);
		i++;
		printf("|%-22s|",presult[i]);
    	i++;
		printf("|%-8s|",presult[i]);
		i++;
		printf("|%-7s|",presult[i]);
		i++;
//		printf("|%-7s|",presult[i]);
//		i++;
		if(i % col == 0)
			printf("\n");
	}
		

}



void out_port(sqlite3 * db)
{
	int i,time1,time2,row,col;
	char ** presult;
	int ret;
	char sql3[100];
	char sql[100];
	char sql2[100];
	char carno[100];
	time_t t2;
	char * errmsg;
	struct tm * tm2;

	time2 = time(&t2);
	tm2 = localtime(&t2);

	printf("请输入要取车的车牌号码:\n");
	scanf("%s",carno);

	sprintf(sql3,"select temp from mytable where carno = '%s';",carno);
	ret = sqlite3_get_table(db,sql3,&presult,&row,&col,&errmsg);
	table_error(ret,"state out",errmsg);
	time1 = atoi(presult[1]);

	sprintf(sql,"update mytable set carno = '',intime = '',state = 'YES' where carno ='%s' and carport <= %d;",carno,NUM_CARPORT);
	ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	table_error(ret,"取车",errmsg);


//	printf("1 %d\n2 %d\n3 %d\n",time1,time2,time2 - time1);
	printf("取车成功！\n\
			一共停车%f小时,共计%f元\n",((double)(time2 - time1)/3600.0),(double)((time2 - time1)/3600) * PRICE);
	
	sprintf(sql2,"update mytable set outtime = '%d-%d-%d %d:%d:%d',money = %f where carport > %d and carno = '%s';",tm2 -> tm_year + 1900,tm2-> tm_mon + 1,tm2->tm_mday,tm2->tm_hour,tm2->tm_min,tm2->tm_sec,((double)((time2 - time1)/3600) * PRICE),NUM_CARPORT,carno);   		ret = sqlite3_exec(db,sql2,NULL,NULL,&errmsg);
	table_error(ret,"备份",errmsg);






}

void his_port(sqlite3 * db)
{
	int i,j;
	int ret;
	char **presult;
	int row,col;
	char *errmsg;
	char sql[100];

	sprintf(sql,"select carno,intime,outtime,money from mytable where carport > %d order by carno",NUM_CARPORT);
	ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);
	table_error(ret,"Look",errmsg);

	for(i = 0;i < (row +1)*col;)
	{
		printf("|%-12s|",presult[i]);
		i++;
		printf("|%-22s|",presult[i]);
		i++;
		printf("|%-22s|",presult[i]);
		i++;
		printf("|%-8s|",presult[i]);
		i++;
		if(i % col == 0)
			printf("\n");
	}

}


void over()
{
	printf("谢谢使用!\n");
	exit(0);
}
void sel_func(sqlite3 * db,int num)
{

	switch(num)
	{
		case 1:in_port(db);break;
		case 2:out_port(db); break;
		case 3:look_port(db);break;
		case 4:his_port(db);break;
		case 5:over();break;
		default:printf("输入错误！\n");break;
	}
}

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

	sqlite3_open("database.db",&db);

	create_table(db);

	make_port(db);

	printf("\n\
                    此停车场一共有%d个车位！\n",NUM_CARPORT);

    while(1)
	{
		num = show();

		sel_func(db,num);
	}
}