using Accord.Statistics.Models.Regression.Linear; using MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace StacjaProb { static class mySqlFunctions { public static MySql.Data.MySqlClient.MySqlConnection connection; // Inicjuje połączenie. // ===================================================== // public static void initConnection() { string myConnectionString; // wczytuje, zapisuje z rejestru aktualne dane const string userRoot = "HKEY_CURRENT_USER\\SoftSPM"; string noSuch = (string)Microsoft.Win32.Registry.GetValue(userRoot, "COMPANY_NAME", ""); if (String.Compare(noSuch, "SoftSPM", true) != 0) { Microsoft.Win32.Registry.SetValue(userRoot + "\\SPM_TST", "HOST_NAME", "localhost"); Microsoft.Win32.Registry.SetValue(userRoot + "\\SPM_TST", "DB_USER", "spm_local"); Microsoft.Win32.Registry.SetValue(userRoot + "\\SPM_TST", "DB_NAME", "softspm_test"); Microsoft.Win32.Registry.SetValue(userRoot + "\\SPM_TST", "DB_USER_PASSWORD", "ff8efe59e7265fe9680b0c325e44cb53"); Microsoft.Win32.Registry.SetValue(userRoot, "COMPANY_NAME", "SoftSPM"); Microsoft.Win32.Registry.SetValue(userRoot, "COMPANY_OWNER", "Przemysław Zatorski"); Microsoft.Win32.Registry.SetValue(userRoot, "COMPANY_CONTACT", "https://softspm.com"); Microsoft.Win32.Registry.SetValue(userRoot, "PRODUCT_KEY", ""); Microsoft.Win32.Registry.SetValue(userRoot + "\\SPM_TST", "MODBUS_TCP", "193.43.126.199"); Microsoft.Win32.Registry.SetValue(userRoot + "\\SPM_TST", "MODBUS_PORT", "502"); } else { vGlobal.vMySql_hostname = Microsoft.Win32.Registry.GetValue(userRoot + "\\SPM_TST", "HOST_NAME", "").ToString(); vGlobal.vMySql_user = Microsoft.Win32.Registry.GetValue(userRoot + "\\SPM_TST", "DB_USER", "").ToString(); vGlobal.vMySql_pass = Microsoft.Win32.Registry.GetValue(userRoot + "\\SPM_TST", "DB_USER_PASSWORD", "").ToString(); vGlobal.vMySql_dbname = Microsoft.Win32.Registry.GetValue(userRoot + "\\SPM_TST", "DB_NAME", "").ToString(); vGlobal.softwareProductKeyVerify = Microsoft.Win32.Registry.GetValue(userRoot, "PRODUCT_KEY", "").ToString(); } myConnectionString = "server=" + vGlobal.vMySql_hostname + ";uid=" + vGlobal.vMySql_user + ";pwd=" + vGlobal.vMySql_pass + ";database=" + vGlobal.vMySql_dbname + ";"; connection = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString); } // ===================================================== // //open connection to database // ===================================================== // public static bool OpenConnection() { try { connection.Open(); return true; } catch { return false; } } // ===================================================== // //Close connection // ===================================================== // public static bool CloseConnection() { try { connection.Close(); return true; } catch { return false; } } // ===================================================== // //Weryfikacja klucza produktu po wdrożeniu. // ===================================================== // public static int verifyProductKey() { int res = 0; DateTime curDate = DateTime.Today; //sprawdzanie if (vGlobal.softwareProductTrialDate.CompareTo(curDate) < 0) { if (String.Compare(vGlobal.softwareProductKey, vGlobal.softwareProductKeyVerify, true) == 0) res = 1; } else res = 1; return res; } // ===================================================== // //Insert statement // ===================================================== // public static void Insert(string query) { string myConnect = "server=" + vGlobal.vMySql_hostname + ";uid=" + vGlobal.vMySql_user + ";pwd=" + vGlobal.vMySql_pass + ";database=" + vGlobal.vMySql_dbname + ";"; MySql.Data.MySqlClient.MySqlConnection _conn = new MySql.Data.MySqlClient.MySqlConnection(myConnect); try { _conn.Open(); MySql.Data.MySqlClient.MySqlCommand _cmd = new MySql.Data.MySqlClient.MySqlCommand(query, _conn); _cmd.ExecuteNonQuery(); _conn.Close(); } catch { } } // ===================================================== // public static string getPolyRegresionModel(int pump_id, int reg_val) { string model=""; List x = new List(); List y = new List(); initConnection(); if (OpenConnection()) { // Ładuje listę kroków MySql.Data.MySqlClient.MySqlCommand _cmd = new MySql.Data.MySqlClient.MySqlCommand("select PEP_X_PARAM,PEP_Y_PARAM from seg_pump_eng_param where PEP_PUP_ID=" + pump_id + " and PEP_TYPE='H' order by PEP_X_PARAM", mySqlFunctions.connection); MySql.Data.MySqlClient.MySqlDataReader _dataReader = _cmd.ExecuteReader(); while (_dataReader.Read()) { x.Add(Convert.ToDouble(_dataReader["PEP_X_PARAM"].ToString())); y.Add(Convert.ToDouble(_dataReader["PEP_Y_PARAM"].ToString())); } _dataReader.Close(); double[] x_wsp = x.ToArray(); double[] y_wsp = y.ToArray(); model = Accord.Statistics.Models.Regression.Linear.PolynomialRegression.FromData(reg_val, x_wsp, y_wsp).ToString(); CloseConnection(); } else model = ""; return model; } // ===================================================== // public static string getPolyRegresionModel_ETA(int pump_id, int reg_val) { string model=""; List x = new List(); List y = new List(); initConnection(); if (OpenConnection()) { // Ładuje listę kroków MySql.Data.MySqlClient.MySqlCommand _cmd = new MySql.Data.MySqlClient.MySqlCommand("select PEP_X_PARAM,PEP_Y_PARAM from seg_pump_eng_param where PEP_PUP_ID=" + pump_id + " and PEP_TYPE='ETA' order by PEP_X_PARAM", mySqlFunctions.connection); MySql.Data.MySqlClient.MySqlDataReader _dataReader = _cmd.ExecuteReader(); while (_dataReader.Read()) { x.Add(Convert.ToDouble(_dataReader["PEP_X_PARAM"].ToString())); y.Add(Convert.ToDouble(_dataReader["PEP_Y_PARAM"].ToString())); } _dataReader.Close(); double[] x_wsp = x.ToArray(); double[] y_wsp = y.ToArray(); model = Accord.Statistics.Models.Regression.Linear.PolynomialRegression.FromData(reg_val, x_wsp, y_wsp).ToString(); CloseConnection(); } else model = ""; return model; } //Zwraca max Q dla zadanej pompy (zaokrąglając do 50 w górę) // ===================================================== // public static int getQmaxforPumpID(int pumpId) { int res=0; double tmp=0; initConnection(); if (OpenConnection()) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("select max(PEP_X_PARAM) from seg_pump_eng_param where PEP_PUP_ID=" + pumpId.ToString(), mySqlFunctions.connection); tmp = Math.Ceiling(Convert.ToDouble(cmd.ExecuteScalar()) / 10); res = Convert.ToInt32(tmp * 10); CloseConnection(); } return res; } // ===================================================== // //Zwraca max H dla zadanej pompy (zaokrąglając do 50 w górę) // ===================================================== // public static int getHmaxforPumpID(int pumpId) { int res = 0; double tmp = 0; initConnection(); if (OpenConnection()) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("select max(PEP_Y_PARAM) from seg_pump_eng_param where PEP_PUP_ID=" + pumpId.ToString(), mySqlFunctions.connection); tmp = Math.Ceiling(Convert.ToDouble(cmd.ExecuteScalar()) / 50); res = Convert.ToInt32(tmp * 50); CloseConnection(); } return res; } //Zwraca max P dla zadanej pompy (zaokrąglając do 50 w górę) // ===================================================== // public static int getPmaxforTestID(int testId) { int res = 0; double tmp = 0; initConnection(); if (OpenConnection()) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand("select max(TSD_VAL_14) from tst_test_details where TSD_TST_ID=" + testId.ToString(), mySqlFunctions.connection); //tmp = Math.Ceiling(Convert.ToDouble(cmd.ExecuteScalar()) / 50); tmp = Math.Ceiling(Convert.ToDouble(cmd.ExecuteScalar()) + 5); res = Convert.ToInt32(tmp); CloseConnection(); } return res; } //znajduje najwyższy punkt i dodaje 10% dla budowani skali dla wykresu raportu - po próbie // =============================================================================================================================================================== public static int getScaledMaxForPumpAndTestWithRegression(int pumpId, int testId) { double hMax = 0; double pMax = 0; double yAtZero = 0; int result = 0; initConnection(); if (OpenConnection()) { try { using (var cmd = new MySqlCommand()) { cmd.Connection = mySqlFunctions.connection; // --- 1️⃣ Pobierz max wysokość (H) dla pompy --- cmd.CommandText = "SELECT MAX(PEP_Y_PARAM) FROM seg_pump_eng_param WHERE PEP_PUP_ID = @pumpId"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@pumpId", pumpId); object hObj = cmd.ExecuteScalar(); if (hObj != DBNull.Value && hObj != null) hMax = Math.Ceiling(Convert.ToDouble(hObj) / 50.0) * 50.0; // --- 2️⃣ Pobierz max ciśnienie (P) dla testu --- cmd.CommandText = "SELECT MAX(TSD_VAL_14) FROM tst_test_details WHERE TSD_TST_ID = @testId"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@testId", testId); object pObj = cmd.ExecuteScalar(); if (pObj != DBNull.Value && pObj != null) pMax = Math.Ceiling(Convert.ToDouble(pObj) + 5.0); // --- 3️⃣ Pobierz dane (X, Y) do regresji wielomianowej --- cmd.CommandText = "SELECT TSD_VAL_04, TSD_VAL_08 FROM tst_test_details WHERE TSD_TST_ID = @testId ORDER BY TSD_VAL_04"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@testId", testId); List xList = new List(); List yList = new List(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { if (double.TryParse(reader["TSD_VAL_04"].ToString(), out double xv) && double.TryParse(reader["TSD_VAL_08"].ToString(), out double yv)) { xList.Add(xv); yList.Add(yv); } } } // --- 4️⃣ Regresja 3. stopnia i obliczenie Y(0) --- if (xList.Count >= 4) // min. 4 punkty (zalecane >=5) { double[] xArr = xList.ToArray(); double[] yArr = yList.ToArray(); // Użycie tak jak w Twojej funkcji GetPolynomialValue: var model = PolynomialRegression.FromData(3, xArr, yArr); double pred = model.Transform(0.0); if (!double.IsNaN(pred) && !double.IsInfinity(pred)) yAtZero = pred; } // --- 5️⃣ Wybierz największą wartość i dodaj 10% --- double maxVal = Math.Max(hMax, Math.Max(pMax, yAtZero)); if (maxVal < 0) maxVal = 0; double scaledVal = maxVal * 1.10; // --- 6️⃣ Zaokrąglij w górę i zwróć jako INT --- result = Convert.ToInt32(Math.Ceiling(scaledVal)); } } catch (Exception ex) { Console.WriteLine($"Błąd podczas pobierania danych lub obliczeń: {ex.Message}"); } finally { CloseConnection(); } } return result; } // =============================================================================================================================================================== //znajduje najwyższy punkt i dodaje 20% dla budowani skali dla wykresu raportu - przed próbą dla wykresu na pulpicie // =============================================================================================================================================================== public static int getScaledMaxForPumpWithRegression(int pumpId) { double yMaxFromSql = 0; double yAtZero = 0; int result = 0; initConnection(); if (OpenConnection()) { try { using (var cmd = new MySqlCommand()) { cmd.Connection = mySqlFunctions.connection; // --- 1️ Pobierz maksymalną wartość PEP_Y_PARAM --- cmd.CommandText = "SELECT MAX(PEP_Y_PARAM) FROM seg_pump_eng_param WHERE PEP_PUP_ID = @pumpId"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@pumpId", pumpId); object yObj = cmd.ExecuteScalar(); if (yObj != DBNull.Value && yObj != null) yMaxFromSql = Convert.ToDouble(yObj); // --- 2️ Pobierz dane do regresji --- cmd.CommandText = "SELECT PEP_X_PARAM, PEP_Y_PARAM FROM seg_pump_eng_param WHERE PEP_PUP_ID = @pumpId ORDER BY PEP_X_PARAM"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@pumpId", pumpId); List xList = new List(); List yList = new List(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { if (double.TryParse(reader["PEP_X_PARAM"].ToString(), out double xv) && double.TryParse(reader["PEP_Y_PARAM"].ToString(), out double yv)) { xList.Add(xv); yList.Add(yv); } } } // --- 3️ Regresja 3. stopnia i obliczenie Y(0) --- if (xList.Count >= 4) { double[] xArr = xList.ToArray(); double[] yArr = yList.ToArray(); // jak w Twojej działającej funkcji: stopień = 3 var model = PolynomialRegression.FromData(3, xArr, yArr); double pred = model.Transform(0.0); if (!double.IsNaN(pred) && !double.IsInfinity(pred)) yAtZero = pred; } // --- 4️ Wybierz większą wartość i dodaj 30% --- double maxVal = Math.Max(yMaxFromSql, yAtZero); if (maxVal < 0) maxVal = 0; double scaledVal = maxVal * 1.30; // --- 5️ Zaokrąglij w górę i zwróć jako INT --- result = Convert.ToInt32(Math.Ceiling(scaledVal)); } } catch (Exception ex) { Console.WriteLine($"Błąd podczas pobierania danych lub obliczeń: {ex.Message}"); } finally { CloseConnection(); } } return result; } // =============================================================================================================================================================== // ===================================================== // public static int sqlIntValue(string query) { int res = 0; try { initConnection(); if (OpenConnection()) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query.ToString(), mySqlFunctions.connection); res = Convert.ToInt32(cmd.ExecuteScalar()); //CloseConnection(); } else res = 0; } catch { } return res; } // ===================================================== // public static double sqlDoubleValue(string query) { double res = 0; try { initConnection(); if (OpenConnection()) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query.ToString(), mySqlFunctions.connection); res = Convert.ToDouble(cmd.ExecuteScalar()); //CloseConnection(); } else res = 0; } catch { } return res; } // ===================================================== // public static string sqlStringValue(string query, string field) { string res = ""; try { initConnection(); if (OpenConnection()) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query, mySqlFunctions.connection); MySql.Data.MySqlClient.MySqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { res = dataReader[field].ToString(); } dataReader.Close(); CloseConnection(); } } catch{} return res; } public static void sqlExec(string query, MySql.Data.MySqlClient.MySqlConnection con) { try { initConnection(); if (OpenConnection()) { MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(query, con); cmd.ExecuteNonQuery(); CloseConnection(); } } catch { } } // insertuje logi // ============================================================================================ public static void insertDataLog(int test_id, string typ, string val, string usr) { Insert("INSERT INTO tst_test_logs SET TLG_TST_TD=" + test_id + ",TLG_TYPE='" + typ + "',TLG_VALUE='" + val + "',TLG_AUDIT_CU='" + usr + "',TLG_AUDIT_CD=now()"); } // // po naciśnięciu start generuje wpis jakonowa próba i zwraca ID próby // ============================================================================================ public static int createNewTest(int stan_id, int pump_id, string pump_name, string pump_sn, int eng_id, string eng_name, string eng_sn, string stan_izol, string usr) { int res = 0; initConnection(); if (OpenConnection()) { Insert("INSERT INTO tst_tests SET TST_TPL_ID=" + stan_id.ToString() + ",TST_PUP_ID=" + pump_id.ToString() + ",TST_PUP_NAME='"+ pump_name + "',TST_ENG_NAME='"+ eng_name + "',TST_PRT_ID='"+vGlobal.app_participants_id.ToString()+ "',TST_PUP_SN_NO='" + pump_sn + "',TST_ENG_ID=" + eng_id.ToString() + ",TST_ENG_SN_NO='" + eng_sn + "',TST_STAN_COLD='" + stan_izol + "',TST_AUDIT_CU='" + usr + "',TST_AUDIT_CD=now()"); res = sqlIntValue("select max(TST_ID) from tst_tests"); CloseConnection(); } return res; } public static void setXYformulaForPoly3and6(int pup_id) { string funkcja = ""; mySqlFunctions.Insert("delete from seg_pumps_xy_formula where PUF_PUP_ID=" + pup_id + " and (PUF_TYPE='TST_H3' or PUF_TYPE='TST_H6')"); funkcja = mySqlFunctions.getPolyRegresionModel(pup_id, 3); mySqlFunctions.Insert("INSERT INTO seg_pumps_xy_formula SET PUF_PUP_ID=" + pup_id + ",PUF_XY_FORMULA='" + funkcja + "',PUF_TYPE='TST_H3'"); funkcja = mySqlFunctions.getPolyRegresionModel_ETA(pup_id, 3); mySqlFunctions.Insert("INSERT INTO seg_pumps_xy_formula SET PUF_PUP_ID=" + pup_id + ",PUF_XY_FORMULA='" + funkcja + "',PUF_TYPE='TST_ETA3'"); //funkcja = mySqlFunctions.getPolyRegresionModel(pup_id, 6); //mySqlFunctions.Insert("INSERT INTO seg_pumps_xy_formula SET PUF_PUP_ID=" + pup_id + ",PUF_XY_FORMULA='" + funkcja + "',PUF_TYPE='TST_H6'"); } public static string getTimeToZasuwa(int timInSekundy) { string res = "M"; //timInSekundy = timInSekundy * 2; if (timInSekundy<16) res = timInSekundy.ToString("X"); else { if (timInSekundy==16) res = "G"; else if (timInSekundy==17) res = "H"; else if (timInSekundy==18) res = "I"; else if (timInSekundy==19) res = "J"; else if (timInSekundy==20) res = "K"; else if (timInSekundy==21) res = "L"; else if (timInSekundy==22) res = "M"; else if (timInSekundy==23) res = "N"; else if (timInSekundy==24) res = "O"; else if (timInSekundy==25) res = "P"; else if (timInSekundy==26) res = "R"; else if (timInSekundy==27) res = "S"; else if (timInSekundy==28) res = "T"; else if (timInSekundy==29) res = "U"; else if (timInSekundy==30) res = "W"; else if (timInSekundy==31) res = "Y"; else if (timInSekundy==32) res = "Z"; } return res; } } }