/// <summary>
/// Obtains the fields in a table
/// </summary>
/// <param name="connection"></param>
/// <param name="_source"></param>
/// <param name="database"></param>
/// <param name="owner"></param>
/// <param name="table"></param>
/// <returns></returns>
public QvDataContractResponse getFields(QvGamsConnection connection, string _source, string database, string owner, string table)
{
string file = database + "\\" + owner;
if (database != string.Empty && owner != string.Empty)
{
using (GAMSHelper gh = new GAMSHelper(_source, file))
{
gh.LoadGAMSFile(connection);
var currentTable = connection.FindTable(table, connection.MTables);
return(new QvDataContractFieldListResponse
{
qFields = (currentTable != null) ? currentTable.Fields : new QvxField[0]
});
}
}
else
{
return(new QvDataContractFieldListResponse
{
qFields = new QvxField[0]
});
}
}
/// <summary>
/// Parses the query and fills the following properties: fields, liveTable, file, where
/// </summary>
/// <param name="query"></param>
/// <param name="qvxTables"></param>
/// <returns>the table in use</returns>
public override QvxDataTable ExtractQuery(string query, List <QvxTable> qvxTables)
{
#if DEBUG
QvxLog.Log(QvxLogFacility.Application, QvxLogSeverity.Notice, "ExtractQuery()");
#endif
QueryExtractor.ExtractQueryDetails(query, out selectedFields, out string liveTable, out file, out where);
using (GAMSHelper gh = new GAMSHelper(_source, file))
{
gh.LoadGAMSFile(this);
qTable = FindTable(liveTable, MTables);
if (qTable == null)
{
throw new QvxPleaseSendReplyException(QvxResult.QVX_TABLE_NOT_FOUND, String.Format("The symbol \"{0}\" is not valid", liveTable));
}
// create a list with all fields that appear in SELECT and WHERE
HashSet <string> referencedFields = new HashSet <string>(selectedFields);
referencedFields.UnionWith(where.ConvertAll(item => item.Field));
// before returning the table to Qlik, we check if the fields were selected by position or name, in order to use the name selected by the user
Regex rx = new Regex("^@([0-9]+)$");
foreach (string referencedField in referencedFields)
{
Match match = rx.Match(referencedField);
if (match.Success)
{
// the column is selected by position
Group group = match.Groups[1];
int columnPosition = int.Parse(group.Value);
if (columnPosition >= qTable.Fields.Length)
{
throw new QvxPleaseSendReplyException(QvxResult.QVX_FIELD_NOT_FOUND, String.Format("The field position \"{0}\" is not valid", referencedField));
}
else if (referencedFields.Contains(qTable.Fields[columnPosition].FieldName))
{
throw new QvxPleaseSendReplyException(QvxResult.QVX_FIELD_NOT_FOUND, String.Format("The same field cannot be selected by position and name: \"{0}\", \"{1}\"",
referencedField, qTable.Fields[columnPosition].FieldName));
}
else
{
// we update the QvxTable, so internally the field is always called by the name selected by the user
qTable.Fields[columnPosition].FieldName = referencedField;
}
}
else
{
// the column is selected by name, so we only check if the name is right
if (!Array.Exists(qTable.Fields, tableField => tableField.FieldName.Equals(referencedField)))
{
throw new QvxPleaseSendReplyException(QvxResult.QVX_FIELD_NOT_FOUND, String.Format("The field \"{0}\" is not valid", referencedField));
}
}
}
return(new QvxDataTable(qTable));
}
}
/// <summary>
/// Obtains the data for the preview using the high level Gams API
/// </summary>
/// <param name="connection"></param>
/// <param name="_source"></param>
/// <param name="database"></param>
/// <param name="owner"></param>
/// <param name="tableName"></param>
/// <returns></returns>
private PreviewResponse getPreview(QvGamsConnection connection, string _source, string database, string owner, string tableName)
{
string file = database + "\\" + owner;
var result = new PreviewResponse();
if (database != string.Empty && owner != string.Empty && tableName != string.Empty)
{
using (GAMSHelper gh = new GAMSHelper(_source, file))
{
gh.LoadGAMSFile(connection);
string symbolName = tableName.Trim();
var currentTable = connection.FindTable(tableName, connection.MTables);
// Store the table Header
var row = new PreviewRow();
foreach (var field in currentTable.Fields)
{
row.qValues.Add(field.FieldName);
}
result.qPreview.Add(row);
// Getting the preview data
string[,] PreviewTableData = gh.GetPreviewData(currentTable, 9);
int FinalNumberOfRows = PreviewTableData.GetLength(0);
int NumberOfColumns = PreviewTableData.GetLength(1);
for (int i = 0; i < FinalNumberOfRows; i++)
{
row = new PreviewRow();
for (int j = 0; j < NumberOfColumns; j++)
{
row.qValues.Add(PreviewTableData[i, j]);
}
result.qPreview.Add(row);
}
}
}
return(result);
}
/// <summary>
/// Obtains the tables (gams symbols)
/// </summary>
/// <param name="connection"></param>
/// <param name="_source"></param>
/// <param name="database"></param>
/// <param name="owner"></param>
/// <returns></returns>
public QvDataContractResponse getTables(QvGamsConnection connection, string _source, string database, string owner)
{
string file = database + "\\" + owner;
if (database != string.Empty && owner != string.Empty)
{
using (GAMSHelper gh = new GAMSHelper(_source, file))
{
gh.LoadGAMSFile(connection);
return(new QvDataContractTableListResponse
{
qTables = connection.MTables
});
}
}
else
{
return(new QvDataContractTableListResponse
{
qTables = new List <QvxTable>()
});
}
}