Project/plc调度节点/tool/mysql_statistics.cpp

//
// Created by huli on 2024/3/19.
//

#include "mysql_statistics.h"




void sql_test()
{
	for (int i = 1; i <= CAR_WHEEL_BASE_LEVEL_MAX; ++i)
	{
		float t_wheel_base = i*0.05+2.45;

		//执行sql操作
		char update_record_sql[DB_SQL_SIZE];
		memset(update_record_sql, 0, DB_SQL_SIZE);
		sprintf(update_record_sql, "update space set  wheel_base_level = %d where ( wheel_base < %f + 0.001 and wheel_base > %f - 0.001  )  ",
				i, t_wheel_base, t_wheel_base


		);
		LOG(INFO) << " update_record_sql =  "<< update_record_sql << " --- " ;
		Database_controller::get_instance_pointer()->sql_update(update_record_sql);
	}

}

struct TTT
{
	string car_number;
	string leave_time;
	int table_id;
	int exist;
};

int sql_ttt_1(string car_number)
{
	//查询车位表
	char query_sql[DB_SQL_SIZE];
	memset(query_sql, 0, DB_SQL_SIZE);
	sprintf(query_sql,"select * from space where car_number = '%s' ", car_number.c_str());
	boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
	Error_manager ec = Database_controller::get_instance_pointer()->sql_query(query_sql, tp_result);

	if(ec == SUCCESS)
	{
		if(tp_result == nullptr)
		{
			return 0;
		}
		//只取第一条结果, 默认是id最小的,
		if (tp_result->next())
		{
			char buf[DB_SQL_SIZE];
			memset(buf, 0, DB_SQL_SIZE);
			try
			{
				int t_table_id = tp_result->getInt("table_id");

				//执行sql操作
				char update_record_sql[DB_SQL_SIZE];
				memset(update_record_sql, 0, DB_SQL_SIZE);
				sprintf(update_record_sql, "update ttt set  exist = 1, table_id = %d where car_number = '%s' ",t_table_id,  car_number.c_str());
				LOG(INFO) << " update_record_sql =  "<< update_record_sql << " --- " ;
				Database_controller::get_instance_pointer()->sql_update(update_record_sql);


			}
			catch (sql::SQLException &e)
			{
				/* Use what() (derived from std::runtime_error) to fetch the error message */
				sprintf(buf, "# ERR: %s\n (MySQL error code: %d, SQLState: %s", e.what(), e.getErrorCode(), e.getSQLState().c_str());
				return 0;
			}
			catch (std::runtime_error &e)
			{
				sprintf(buf, "# ERR: %s\n ERR: runtime_error in  %s ", e.what(), __FILE__);
				return 0;
			}
		}
		else
		{
			//执行sql操作
			char update_record_sql[DB_SQL_SIZE];
			memset(update_record_sql, 0, DB_SQL_SIZE);
			sprintf(update_record_sql, "update ttt set  exist = 0 where car_number = '%s' ", car_number.c_str());
			LOG(INFO) << " update_record_sql =  "<< update_record_sql << " --- " ;
			Database_controller::get_instance_pointer()->sql_update(update_record_sql);

		}
	}

	return 0;
}


int sql_ttt_2(string car_number)
{
	//查询车位表
	char query_sql[DB_SQL_SIZE];
	memset(query_sql, 0, DB_SQL_SIZE);
	sprintf(query_sql,"select * from record where car_number = '%s' order by count_id DESC limit 1 ", car_number.c_str());
	boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
	Error_manager ec = Database_controller::get_instance_pointer()->sql_query(query_sql, tp_result);

	if(ec == SUCCESS)
	{
		if(tp_result == nullptr)
		{
			return 0;
		}
		//只取第一条结果, 默认是id最小的,
		if (tp_result->next())
		{
			char buf[DB_SQL_SIZE];
			memset(buf, 0, DB_SQL_SIZE);
			try
			{
				int t_table_id = tp_result->getInt("table_id");
				string enqueue_time = tp_result->getString("enqueue_time");
				string in_time_start = tp_result->getString("in_time_start");
				string in_time_end = tp_result->getString("in_time_end");
				string out_time_start = tp_result->getString("out_time_start");
				string out_time_end = tp_result->getString("out_time_end");
				string leave_time;
				if ( out_time_start.size()>2 )
				{
					leave_time=out_time_start;
				}
				else if ( out_time_end.size()>2 )
				{
					leave_time=out_time_end;
				}
				else if ( in_time_end.size()>2 )
				{
					leave_time=in_time_end;
				}
				else if ( in_time_start.size()>2 )
				{
					leave_time=in_time_start;
				}
				else if ( enqueue_time.size()>2 )
				{
					leave_time=enqueue_time;
				}

				//执行sql操作
				char update_record_sql[DB_SQL_SIZE];
				memset(update_record_sql, 0, DB_SQL_SIZE);
				sprintf(update_record_sql, "update ttt set  leave_time = '%s', table_id = %d where car_number = '%s' ",leave_time.c_str(), t_table_id,  car_number.c_str());
				LOG(INFO) << " update_record_sql =  "<< update_record_sql << " --- " ;
				Database_controller::get_instance_pointer()->sql_update(update_record_sql);


			}
			catch (sql::SQLException &e)
			{
				/* Use what() (derived from std::runtime_error) to fetch the error message */
				sprintf(buf, "# ERR: %s\n (MySQL error code: %d, SQLState: %s", e.what(), e.getErrorCode(), e.getSQLState().c_str());
				return 0;
			}
			catch (std::runtime_error &e)
			{
				sprintf(buf, "# ERR: %s\n ERR: runtime_error in  %s ", e.what(), __FILE__);
				return 0;
			}
		}
		else
		{
			std::this_thread::sleep_for(std::chrono::milliseconds(1));
			//执行sql操作
			char update_record_sql[DB_SQL_SIZE];
			memset(update_record_sql, 0, DB_SQL_SIZE);
			sprintf(update_record_sql, "update ttt set  exist = 0 where car_number = '%s' ", car_number.c_str());
			LOG(INFO) << " update_record_sql =  "<< update_record_sql << " --- " ;
			Database_controller::get_instance_pointer()->sql_update(update_record_sql);

		}
	}

	return 0;
}

int sql_ttt_3()
{
	//查询车位表
	char query_sql[DB_SQL_SIZE];
	memset(query_sql, 0, DB_SQL_SIZE);
	sprintf(query_sql,"select car_number from ttt ");
	boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
	Error_manager ec = Database_controller::get_instance_pointer()->sql_query(query_sql, tp_result);

	if(ec == SUCCESS)
	{
		if(tp_result == nullptr)
		{
			return 0;
		}
		//只取第一条结果, 默认是id最小的,
		while (tp_result->next())
		{
			char buf[DB_SQL_SIZE];
			memset(buf, 0, DB_SQL_SIZE);
			try
			{
				string t_car_number = tp_result->getString("car_number");

				sql_ttt_1(t_car_number);

			}
			catch (sql::SQLException &e)
			{
				/* Use what() (derived from std::runtime_error) to fetch the error message */
				sprintf(buf, "# ERR: %s\n (MySQL error code: %d, SQLState: %s", e.what(), e.getErrorCode(), e.getSQLState().c_str());
				return 0;
			}
			catch (std::runtime_error &e)
			{
				sprintf(buf, "# ERR: %s\n ERR: runtime_error in  %s ", e.what(), __FILE__);
				return 0;
			}
		}
	}

	return 0;
}


int sql_ttt_4()
{
	//查询车位表
	char query_sql[DB_SQL_SIZE];
	memset(query_sql, 0, DB_SQL_SIZE);
	sprintf(query_sql,"select car_number from ttt where exist =0 ");
	boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
	Error_manager ec = Database_controller::get_instance_pointer()->sql_query(query_sql, tp_result);

	if(ec == SUCCESS)
	{
		if(tp_result == nullptr)
		{
			return 0;
		}
		//只取第一条结果, 默认是id最小的,
		while (tp_result->next())
		{
			char buf[DB_SQL_SIZE];
			memset(buf, 0, DB_SQL_SIZE);
			try
			{
				string t_car_number = tp_result->getString("car_number");

				sql_ttt_2(t_car_number);

			}
			catch (sql::SQLException &e)
			{
				/* Use what() (derived from std::runtime_error) to fetch the error message */
				sprintf(buf, "# ERR: %s\n (MySQL error code: %d, SQLState: %s", e.what(), e.getErrorCode(), e.getSQLState().c_str());
				return 0;
			}
			catch (std::runtime_error &e)
			{
				sprintf(buf, "# ERR: %s\n ERR: runtime_error in  %s ", e.what(), __FILE__);
				return 0;
			}
		}
	}

	return 0;
}


//统计进车数量（指定的单元指定的日期）
int mysql_collect_count_for_enqueue(int year, int month, int day, int unit, bool is_insert_row)
{
	//查询车位表
	char query_sql[DB_SQL_SIZE];
	memset(query_sql, 0, DB_SQL_SIZE);
	sprintf(query_sql,"select count(*) from record where enqueue_time like '%04d-%02d-%02d%%' and unit_id = %d ", year, month, day, unit);
//	LOG(INFO) << " query_sql =  "<< query_sql << " --- " ;
	boost::shared_ptr<sql::ResultSet>  tp_result = nullptr;
	Error_manager ec = Database_controller::get_instance_pointer()->sql_query(query_sql, tp_result);

	if(ec == SUCCESS)
	{
		if(tp_result == nullptr)
		{
			return 0;
		}
		//只取第一条结果, 默认是id最小的,
		if (tp_result->next())
		{
			char buf[DB_SQL_SIZE];
			memset(buf, 0, DB_SQL_SIZE);
			try
			{
				int t_count = tp_result->getInt("count(*)");


				//执行sql操作
				if ( is_insert_row )
				{
					char insert_sql[DB_SQL_SIZE];
					memset(insert_sql, 0, DB_SQL_SIZE);
					sprintf(insert_sql, "INSERT INTO statistics (enqueue_day, `%d`) values ('%04d-%02d-%02d', %d) ", unit, year, month, day, t_count);
					LOG(INFO) << " update_record_sql =  "<< insert_sql << " --- " ;
					Database_controller::get_instance_pointer()->sql_update(insert_sql);
				}
				else
				{
					char update_sql[DB_SQL_SIZE];
					memset(update_sql, 0, DB_SQL_SIZE);
					sprintf(update_sql, "update statistics set `%d` = %d where enqueue_day = '%04d-%02d-%02d' ", unit, t_count, year, month, day);
//					LOG(INFO) << " update_record_sql =  "<< update_sql << " --- " ;
					Database_controller::get_instance_pointer()->sql_update(update_sql);
				}



			}
			catch (sql::SQLException &e)
			{
				/* Use what() (derived from std::runtime_error) to fetch the error message */
				sprintf(buf, "# ERR: %s\n (MySQL error code: %d, SQLState: %s", e.what(), e.getErrorCode(), e.getSQLState().c_str());
				return 0;
			}
			catch (std::runtime_error &e)
			{
				sprintf(buf, "# ERR: %s\n ERR: runtime_error in  %s ", e.what(), __FILE__);
				return 0;
			}
		}
		else
		{

		}
	}

	return 0;

	return 0;
}
//统计一个月的进车数量（指定的单元指定的日期）
int mysql_collect_daily_data_all_unit(int year, int month, int first_day, int end_day)
{
	for (int i = first_day; i <= end_day; ++i)
	{
		mysql_collect_count_for_enqueue(year, month, i, 11, true);
		mysql_collect_count_for_enqueue(year, month, i, 12, false);
		mysql_collect_count_for_enqueue(year, month, i, 13, false);
		mysql_collect_count_for_enqueue(year, month, i, 14, false);
		mysql_collect_count_for_enqueue(year, month, i, 21, false);
		mysql_collect_count_for_enqueue(year, month, i, 22, false);
		mysql_collect_count_for_enqueue(year, month, i, 23, false);
		mysql_collect_count_for_enqueue(year, month, i, 24, false);
		mysql_collect_count_for_enqueue(year, month, i, 25, false);
		mysql_collect_count_for_enqueue(year, month, i, 31, false);
		mysql_collect_count_for_enqueue(year, month, i, 32, false);
	}

	return 0;
}