// sql_server_test.cpp : 此文件包含 "main" 函数。程序执行将在此处开始并结束。
//

#include <iostream>
#include<stdio.h>
#include<string.h>
#include<windows.h>
#include<sql.h>
#include<sqlext.h>
#include<sqltypes.h>
#include <ctime>
#include "../ysp_modbus_slave.h"

HANDLE hMutex_database;   //定义互斥对象  

//入参：time_fmt 格式为: 2023-09-01 12:32:43
//出参: 距离1970的对应的格林秒数
//mktime(t)受系统时区设置的影响 如果是东八区，会减掉入参8个小时 如果是0时区，则不减，直接转
// 注意：因为mktime的特性 所以入参 time_fmt必须是跟系统时区设置相关得到的格式化时间
void GetTimeFromFormat(const char* time_fmt, int* time_h32, int* time_l32)
{
	struct tm tm_s = { 0 };
	int year = 0, month = 0, day = 0, hour = 0, min = 0, sec = 0;
	
	year = atoi(time_fmt);
	month = atoi(time_fmt + 4);
	day = atoi(time_fmt + 7);
	hour = atoi(time_fmt + 11);
	min = atoi(time_fmt + 14);
	sec = atoi(time_fmt + 17);
	//09 atoi 是 -9
	if (month < 0)
		month = 0 - month;
	if (day < 0)
		day = 0 - day;
	if (hour < 0)
		hour = 0 - hour;
	if (min < 0)
		min = 0 - min;
	if (sec < 0)
		sec = 0 - sec;
	tm_s.tm_year = year - 1900;
	tm_s.tm_mon = month - 1;
	tm_s.tm_mday = day;
	tm_s.tm_hour = hour;
	tm_s.tm_min = min;
	tm_s.tm_sec = sec;


	//mktime受系统时区设置的影响 如果是东八区，会减掉入参8个小时 如果是0时区，则不减，直接转
	//即mktime得到是当前对应格林秒数(距离1970)
	time_t t1 = mktime(&tm_s);
	printf("data timestamp (str):%s\n", time_fmt);
	printf("data Distance Greenwich  Mean Time (s):%ld\n", t1);
	*time_h32 = t1 >> 32 & 0x00000000FFFFFFFF;
	*time_l32 = t1  & 0x00000000FFFFFFFF;
}
//#include <sqlext.h>
/*数据库中的实际表结构*/
typedef struct DataGas_table {
	int ID;//类型为自增，所以无法软件insert
	int DeviceID;
	char datetime[20];//格式: 1990-07-04 23:04:12
	float H2;
	float CO;
	float CO2;
	float CH4;
	float C2H2;
	float C2H4;
	float C2H6;
	float Total;
	char RawData[2];//该软件中用不到，实际表中为varbinary(max)类型
	char Result[2];//实际为varchar
	char ProcessedData[2];//该软件中用不到，实际表中为varbinary(max)类型
}DataGas_tab_t;

#define DATAGAS_INIT(data)					 \
		do {									\
			DataGas_tab_t data;						\
			memset(&data, 0, sizeof(data));		\
		}while (0) 

#define DATAGAS_INIT_DEFAULT(data)						 \
		do {									\
								\
			memset(&data, 67, sizeof(data));		\
			memset(&data.datetime, 0, sizeof(data.datetime)); \
			memcpy(data.datetime, "2023-09-04 17:24:44", sizeof("2023-09-04 17:24:44"));	\
			data.RawData[0] = 2;\
			data.ProcessedData[0] = 2;\
			data.RawData[1] = 0;\
			data.Result[1] = 0;\
			data.ProcessedData[1] = 0;\
		}while (0) 

SQLRETURN ret;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
//DeviceID, ReadDate, H2, CO, CO2, CH4, C2H2, C2H4, C2H6, Total, RawData, Result, ProcessedData
int DisplayError(SQLCHAR* e1, int e2, const char* msg, int len)
{
	std::cout << "msg:" << msg << std::endl;
	return 0;
}
int Sql_DataGas_insert(DataGas_tab_t data)
{
	SQLCHAR       SqlState[6], SQLStmt[100], Msg[SQL_MAX_MESSAGE_LENGTH];
	SQLINTEGER    NativeError;
	SQLSMALLINT   i, MsgLen;
	SQLRETURN     rc1, rc2;

	char sql_cmd[1024] = { 0 };

	const char* fmt = "insert into OCD_DB.dbo.DataGas (DeviceID, ReadDate, H2, CO, CO2, CH4, C2H2, C2H4, C2H6, Total, RawData, Result, ProcessedData) values(%d,\'%s\',%f,%f,%f,%f,%f,%f,%f,%f,%d,\'%s\',%d)";
	snprintf(sql_cmd, sizeof(sql_cmd), fmt, data.DeviceID, data.datetime, data.H2, data.CO, data.CO2, data.CH4, data.C2H2, data.C2H4, data.C2H6, data.Total, 5, data.Result, 6);
	std::cout << "data.datetime " << data.datetime << std::endl;
	std::cout << "data.H2 " << data.H2 << std::endl;
	std::cout << "data.CO " << data.CO << std::endl;
	std::cout << "data.sql_cmd " << sql_cmd << std::endl;

	ret = SQLExecDirect(hstmt, (SQLCHAR*)sql_cmd, SQL_NTS);
	if (ret != SQL_SUCCESS) {
		std::cout << "insert fail" << std::endl;
	}
	if ((ret == SQL_SUCCESS_WITH_INFO) || (ret == SQL_ERROR)) {
		SQLLEN numRecs = 0;
		SQLGetDiagField(SQL_HANDLE_STMT, hstmt, 0, SQL_DIAG_NUMBER, &numRecs, 0, 0);
		// Get the status records.
		i = 1;
		while (i <= numRecs && (rc2 = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, i, SqlState, &NativeError,
			Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) {
			DisplayError(SqlState, NativeError, (const char*)Msg, MsgLen);
			i++;
		}
	}
	//if(data.DeviceID)
	return 0;
}
int Connect() {
#if 0
	static char init = 0;
	if (init) {
		printf("database connected\n");
		return 0;
	}
#endif
	ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
	ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, SQL_IS_INTEGER);
	ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
	ret = SQLConnect(hdbc, (SQLCHAR*)"ysp", SQL_NTS, (SQLCHAR*)"sa", SQL_NTS, (SQLCHAR*)"123456", SQL_NTS);

	if (!(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)) {
		printf("open database fail!\n");
		return 1;
	}

	//init = 1;
	printf("connect database\n");

	return 0;
}

void free() {
	SQLDisconnect(hdbc);
	SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
	SQLFreeHandle(SQL_HANDLE_ENV, henv);
}



void Insert_data_for_test()
{
#if 1
	DataGas_tab_t data;
	DATAGAS_INIT_DEFAULT(data);
	data.H2 = 99;
	Sql_DataGas_insert(data);
#endif
}

int get_MicroWater_from_database(ysp_modbus_regs_t* ysp_data)
{
	int res = 0;
	if (Connect()) {
		free();
		return 1;
	}

	std::cout << "read microwater data " << std::endl;
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	if (!(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)) {
		std::cout << "SQLAllocHandle fail " << std::endl;
		free();
		return 1;
	}

	SQLPrepare(hstmt, (SQLCHAR*)("select * from OCD_DB.dbo.DataH2O where ID = (select MAX(ID) from OCD_DB.dbo.DataH2O )"), SQL_NTS);
	
	ret = SQLExecute(hstmt);

	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
		if ((ret = SQLFetch(hstmt)) == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {

			//SQLCHAR str9[20], str10[20], str11[20], str12[20], str13[20];
			SQLLEN len_str3, len_str6;
			SQLCHAR time_str[20];
			SQLCHAR PPM[20] = { 0 };

			std::cout << "get micro water data" << std::endl;
#if 0
			SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 20, &len_str1);//ID
			SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 20, &len_str2);

#endif
			SQLGetData(hstmt, 3, SQL_C_CHAR, time_str, 20, &len_str3);//datetime
			SQLGetData(hstmt, 6, SQL_C_CHAR, PPM, 20, &len_str6);//PPM
	
			printf("datetime:%s\n", time_str);
			printf("PPM:%s\n", PPM);

			ysp_data->Water= atof((const char*)PPM);
			
			printf("ysp_data->Water:%f\n", ysp_data->Water);
			//ysp_data->W_timeH32, ysp_data->W_timeL32 这两个字段没有，后面如果有需要加进去
			//GetTimeFromFormat((const char*)time_str, &ysp_data->W_timeH32, &ysp_data->W_timeL32);
		
		}
		else {
			printf("DataH2O table is empty\n");
		}

	}
	else {
		res = 1;//fail
		std::cout << "SQLExecute faild" << std::endl;
	}
	free();
	return res;
}

int get_gas_from_database(ysp_modbus_regs_t* ysp_data) {
	int res = 0;
	if (Connect()) {
		free();
		return 1;
	}

	
	std::cout << "read data " << std::endl;
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	if (!(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)) {
		std::cout << "SQLAllocHandle fail " << std::endl;
		free();
		return 1;
	}
	//Insert_data_for_test();
	//SQLPrepare(hstmt, (SQLCHAR*)("select * from OCD_DB.dbo.DataGas"), SQL_NTS);
	//SQLPrepare(hstmt, (SQLCHAR*)("select * from OCD_DB.dbo.DataGas where ID = 45 "), SQL_NTS);
	SQLPrepare(hstmt, (SQLCHAR*)("select * from OCD_DB.dbo.DataGas where ID = (select MAX(ID) from OCD_DB.dbo.DataGas )"), SQL_NTS);
	//SQLPrepare(hstmt, (SQLCHAR*)("SELECT TOP 1 * FROM  OCD_DB.dbo.DataGas ORDER BY ID DESC"), SQL_NTS);	
	//SQLPrepare(hstmt, (SQLCHAR*)("SELECT* FROM(SELECT*, ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNum FROM OCD_DB.dbo.DataGas) AS SubQuery WHERE RowNum = 2"), SQL_NTS);
	ret = SQLExecute(hstmt);

	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
		if ((ret = SQLFetch(hstmt)) == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {

			//SQLCHAR str9[20], str10[20], str11[20], str12[20], str13[20];
			SQLLEN len_str1, len_str2, len_str3, len_str4;
			SQLCHAR time_str[20];
			SQLCHAR H2[20] = { 0 }, CO[20] = { 0 }, CO2[20] = { 0 }, CH4[20] = { 0 }, C2H2[20] = { 0 }, C2H4[20] = { 0 }, C2H6[20] = { 0 }, Total[20] = { 0 };

			std::cout << "get data" << std::endl;
#if 0
			SQLGetData(hstmt, 1, SQL_C_CHAR, str1, 20, &len_str1);
			SQLGetData(hstmt, 2, SQL_C_CHAR, str2, 20, &len_str2);

#endif
			SQLGetData(hstmt, 3, SQL_C_CHAR, time_str, 20, &len_str3);
			SQLGetData(hstmt, 4, SQL_C_CHAR, H2, 20, &len_str4);
			SQLGetData(hstmt, 5, SQL_C_CHAR, CO, 20, &len_str4);
			SQLGetData(hstmt, 6, SQL_C_CHAR, CO2, 20, &len_str4);
			SQLGetData(hstmt, 7, SQL_C_CHAR, CH4, 20, &len_str4);
			SQLGetData(hstmt, 8, SQL_C_CHAR, C2H2, 20, &len_str4);
			SQLGetData(hstmt, 9, SQL_C_CHAR, C2H4, 20, &len_str4);
			SQLGetData(hstmt, 10, SQL_C_CHAR, C2H6, 20, &len_str4);
			SQLGetData(hstmt, 11, SQL_C_CHAR, Total, 20, &len_str4);


			//printf("%s\t%s\t%s\t%s\n", str1, str2, str3, str4);
			printf("gas time:%s\n", time_str);
			printf("H2:%s\nCO:%s\nCO2:%s\nCH4:%s\n", H2, CO, CO2, CH4);
			printf("C2H2:%s\nC2H4:%s\nC2H6:%s\nTotal:%s\n", C2H2, C2H4, C2H6, Total);

			ysp_data->H2 = atof((const char*)H2);
			printf("----translate\n");
			ysp_data->CO = atof((const char*)CO);
			ysp_data->CO2 = atof((const char*)CO2);
			ysp_data->CH4 = atof((const char*)CH4);
			ysp_data->C2H2 = atof((const char*)C2H2);
			ysp_data->C2H4 = atof((const char*)C2H4);
			ysp_data->C2H6 = atof((const char*)C2H6);
			ysp_data->Total = atof((const char*)Total);
#if 0 //测试字节数据
			char temp1[4] = { 0 };
			memcpy(temp1, &ysp_data->C2H2, 4);
			printf("test: %02x%02x%       02x%02x\n", temp1[0], temp1[1], temp1[2], temp1[3]);
#endif
			printf("H2:%f\nCO:%f\nCO2:%f\nCH4:%f\n", ysp_data->H2, ysp_data->CO, ysp_data->CO2, ysp_data->CH4);
			printf("C2H2:%f\nC2H4:%f\nC2H6:%f\nTotal:%f\n", ysp_data->C2H2, ysp_data->C2H4, ysp_data->C2H6, ysp_data->Total);
			GetTimeFromFormat((const char*)time_str, &ysp_data->time_H32, &ysp_data->time_L32);
		}
		else {
			printf("DataGas table is empty\n");
		}

	}
	else {
		res = 1;//fail
		std::cout << "SQLExecute faild" << std::endl;
	}
	free();
	return res;

}

int fill_ysp_data(ysp_modbus_regs_t * ysp_data)
{
	int ret = 0;
	WaitForSingleObject(hMutex_database, INFINITE);
	
#if 0 //用于测试
	int i = 0;
	uint16_t* ptr;
	ptr = (uint16_t*)ysp_data;
	for (i = 0; i < sizeof(ysp_modbus_regs_t) / 2; i++) {
		ptr[i] = 0x0000 + i;
	}
	ysp_data->H2 =67.87;
	ysp_data->CO = 102;
	ysp_data->CO2 =65;
	ysp_data->CH4 = 67.87;
	ysp_data->C2H2 = 67.87;
	ysp_data->C2H4 = 67.87;
	ysp_data->C2H6 = 67.87;
	ysp_data->Total = 67.87;
	ysp_data->Water = 67.87;
	ysp_data->time_H32 = 0x11223344;
	ysp_data->time_L32 = 0x55667788;

#else 
	printf("read database\n");
	ret = get_gas_from_database(ysp_data);//从数据库读取气体数据
	Sleep(50);
	ret = get_MicroWater_from_database(ysp_data);//从数据库读取微水数据


	//ysp_data->H2 = 67.87;//for test
#endif

	ReleaseMutex(hMutex_database);
	return ret;
}

void xMutex_init()
{
	hMutex_database = CreateMutex(NULL, FALSE, NULL);
}