功能:显示入场时间 现在时间 时间差更新 费用实时更新 离场结算 停车位置剩余数
#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;
}