private bool GetQueryDetails(cDBTools dbt, ref string sql, ref string queryDB, ref Dictionary <string, string> queryParams)
{
string _stage = "";
try
{
//
_stage = $"Getting SQL for query {QueryNumber}";
dbt.Query($"select SQL,Base_Datos from cabecera_consultas where idreg={QueryNumber}");
if (dbt.EOF)
{
throw new Exception("Query not found");
}
sql = "set dateformat dmy " + dbt.FieldValue(0).ToString();
queryDB = dbt.FieldValue(1);
//
_stage = $"Getting parameters for query {QueryNumber}";
dbt.Query($"select Param=Nombre_Parametro from detalle_consultas where consulta={QueryNumber} order by linea");
queryParams = dbt.ToDictionaryKeys();
}
catch (Exception ex)
{
//Console.WriteLine($"[cProcess/GetQueryDetails#{_stage}] {ex.Message}.");
//return false;
throw new Exception($"[cProcess/GetQueryDetails#{_stage}] {ex.Message}");
}
return(true);
}
public async Task Process()
{
string _stage = "";
Dictionary <string, string> _params = null;
string _sql = "", _queryDB = "";
try
{
//
_stage = $"Connecting to {ConnDetailsDB.Server}";
cDBTools _dbt = new cDBTools(ConnDetailsDB);
_dbt.Connect();
//
_stage = "Getting query details";
if (!GetQueryDetails(_dbt, ref _sql, ref _queryDB, ref _params))
{
throw new Exception("Unknown error!");
}
//
_stage = "Processing args string";
Args = cMiscFunctions.CheckArgs(ArgsString);
//
_stage = "Parsing SQL";
if (!ParseSQL(ref _sql, Args, _params))
{
throw new Exception("Unknown error!");
}
//
_stage = $"Changing to {_queryDB} DB";
_dbt.ChangeDB(_queryDB);
//
_stage = "Executing query";
if (!_dbt.Query(_sql))
{
throw new Exception("Unknown error!");
}
//
_stage = "Converting data to dictionary";
if (_dbt.RS.HasRows)
{
Results = _dbt.ToDictionary();
}
// Disconnect from DB
_dbt.Disconnect();
// If no results, exit with empty content. This also works for queries with SubQuery, which have to be treated in a different way
if (Results == null || SubQueryNumber != null)
{
Contents = null;
FileName = null;
Console.WriteLine($">> {QueryNumber}/{ArgsString} completed!");
return;
}
//
switch (FileType)
{
case cMiscFunctions.eFileType.TXT:
// For TXT queries
_stage = "Converting data to TXT";
ProcessTXT();
break;
case cMiscFunctions.eFileType.HTML:
case cMiscFunctions.eFileType.XLS:
case cMiscFunctions.eFileType.PDF:
// Create HTML contents
_stage = "Converting data to HTML";
ProcessHTML();
break;
}
// For XLS and PDF
if (FileType == cMiscFunctions.eFileType.XLS || FileType == cMiscFunctions.eFileType.PDF || FileType == cMiscFunctions.eFileType.TXT)
{
_stage = $"Converting data to {FileType} file";
FileName = ToFile();
}
}
catch (Exception ex)
{
//Console.WriteLine($"[cProcess#Process] {QueryNumber}/{ArgsString}: {ex.Message}");
ErrorMessage = $"[cProcess#Process] {ex.Message}";
Console.WriteLine(ErrorMessage);
// Prepare the _result message for the email.
string _processQueryParams = String.IsNullOrEmpty(ArgsString) ? "NONE" : ArgsString;
string _processQuery = (QueryNumber == null) ? "NONE" : QueryNumber.ToString();
// Match the last occurrence of [xxx#xxx] to ensure it's part of our error message, not part of the system error. This is to show the error message in bold.
Match _match = Regex.Match(ErrorMessage, @"\[([^\[]*)#([^\[]*)\]", RegexOptions.RightToLeft);
int _i = _match.Index + _match.Length;
// Set to bold the error message, ignoring all the "call stack" string, and prepare the html code.
Contents = ErrorMessage.Substring(0, _i) + "<ul><strong>" + ErrorMessage.Substring(_i + 1);
Contents = $"<html><body>Query: {_processQuery}<br>Params: {_processQueryParams}<br>Error:<br>" + Contents.Replace("] ", "]<ul>") + "</strong></body></html>";
//
Error = true;
}
return;
}
