/// <summary>
/// Restituisce la stringa di query base per la misura
/// </summary>
/// <param name="connection">Connessione</param>
/// <param name="measure_coord">Coordinata del database per la misura</param>
/// <param name="start_date">Data di inizio nella clausola WHERE</param>
/// <param name="stop_date">Data di fine nella clausola WHERE</param>
/// <param name="order">Tipo di ordinamento</param>
/// <param name="num_records">Numero di records (0 = massimo concesso)</param>
/// <returns>Stringa di query</returns>
/// <remarks>
/// Per stringa base si intende una query compilata nelle specifiche SELECT, FROM e WHERE (solo per tabelle relazionali),
/// con la possibilità di aggiungere parametri.
/// </remarks>
string GetBaseQueryStr(WetDBConn connection, MeasureDBCoord_Struct measure_coord, DateTime start_date, DateTime stop_date, WetDBConn.OrderTypes order, ulong num_records)
{
string query;
switch (connection.GetProvider())
{
default:
query = string.Empty;
break;
case WetDBConn.ProviderType.ARCHESTRA_SQL:
query = "SELECT ";
if (num_records > 0)
query += "TOP " + num_records.ToString() + " ";
query += "Format(Datetime,'yyyy-MM-dd HH:mm:ss') AS " + measure_coord.timestamp_column + ", Format(Value, '#########0.00') AS '" + measure_coord.value_column + "' FROM " + measure_coord.table_name +
" WHERE History.TagName = '" + measure_coord.value_column + "'" +
" AND vValue IS NOT NULL " +
"AND (Quality = 0 OR Quality = 1) " +
"AND (QualityDetail = 192 OR QualityDetail = 202 OR QualityDetail = 64) " +
//"AND wwResolution = " + ((int)(config.interpolation_time * 60 * 1000)).ToString() + " " +
//"AND wwRetrievalMode = 'Cyclic' " +
"AND wwRetrievalMode = 'Full' " +
"AND DateTime > CONVERT(datetime, '" + start_date.ToString(WetDBConn.MYSQL_DATETIME_FORMAT) + "', 120) " +
"AND DateTime <= CONVERT(datetime, '" + stop_date.ToString(WetDBConn.MYSQL_DATETIME_FORMAT) + "', 120) ORDER BY " +
measure_coord.timestamp_column + (order == WetDBConn.OrderTypes.ASC ? " ASC" : " DESC");
break;
case WetDBConn.ProviderType.IFIX_SQL:
query = "SELECT ";
if (num_records > 0)
query += "TOP " + num_records.ToString() + " ";
query += "* FROM OPENQUERY(IHIST,'SELECT timestamp AS " + measure_coord.timestamp_column +
", value AS " + measure_coord.value_column + " FROM " + measure_coord.table_name +
" WHERE Tagname = " + measure_coord.value_column +
" AND quality = 100 AND timestamp > ''" + start_date.ToString(WetDBConn.MYSQL_DATETIME_FORMAT) +
"'' AND timestamp <= ''" + stop_date.ToString(WetDBConn.MYSQL_DATETIME_FORMAT) + "'' AND samplingmode = LAB ORDER BY " +
measure_coord.timestamp_column + (order == WetDBConn.OrderTypes.ASC ? " ASC" : " DESC") + "')";
break;
case WetDBConn.ProviderType.EXCEL:
query = "SELECT ";
if (num_records > 0)
query += "TOP " + num_records.ToString() + " ";
query += measure_coord.timestamp_column + ", " + measure_coord.value_column + " FROM " + measure_coord.table_name +
" WHERE (" + measure_coord.timestamp_column + " > #" + start_date.ToString(WetDBConn.MYSQL_DATETIME_FORMAT) +
"# AND " + measure_coord.timestamp_column + " <= #" + stop_date.ToString(WetDBConn.MYSQL_DATETIME_FORMAT) + "#)" +
" ORDER BY " + measure_coord.timestamp_column + " " + (order == WetDBConn.OrderTypes.ASC ? "ASC" : "DESC");
break;
case WetDBConn.ProviderType.GENERIC_MYSQL:
query = "SELECT `" + measure_coord.timestamp_column + "`, `" + measure_coord.value_column + "` FROM " + measure_coord.table_name +
" WHERE ";
if (measure_coord.relational_id_column != string.Empty)
{
query += "`" + measure_coord.relational_id_column + "` = ";
switch (measure_coord.relational_id_type)
{
case WetDBConn.PrimaryKeyColumnTypes.REAL:
query += measure_coord.relational_id_value.Replace(',', '.');
break;
case WetDBConn.PrimaryKeyColumnTypes.DATETIME:
query += "'" + Convert.ToDateTime(measure_coord.relational_id_value).ToString(WetDBConn.MYSQL_DATETIME_FORMAT) + "'";
break;
case WetDBConn.PrimaryKeyColumnTypes.TEXT:
query += "'" + measure_coord.relational_id_value + "'";
break;
default:
query += measure_coord.relational_id_value;
break;
}
query += " AND ";
}
query += "(`" + measure_coord.timestamp_column + "` > '" + start_date.ToString(WetDBConn.MYSQL_DATETIME_FORMAT) +
"' AND `" + measure_coord.timestamp_column + "` <= '" + stop_date.ToString(WetDBConn.MYSQL_DATETIME_FORMAT) + "')";
query += " ORDER BY `" + measure_coord.timestamp_column + "` " + (order == WetDBConn.OrderTypes.ASC ? "ASC" : "DESC");
if (num_records > 0)
query += " LIMIT " + num_records.ToString();
break;
}
return query;
}
