public override QvxDataTable ExtractQuery(string query, List <QvxTable> tables)
{
try
{
AppInstance.LoadMemory();
logger.Debug($"Parse query {query}");
var script = ScriptCode.Parse(query);
if (script == null)
{
throw new Exception("The sql script is not valid.");
}
var parameter = ConnectorParameter.Create(MParameters);
var qvxTable = GetData(script, parameter);
var result = new QvxDataTable(qvxTable);
result.Select(qvxTable.Fields);
AppInstance.SaveMemory();
AppInstance.Dispose();
logger.Debug($"Send result table {qvxTable.TableName}");
return(result);
}
catch (Exception ex)
{
logger.Error(ex, "The query could not be executed.");
LogManager.Flush();
return(new QvxDataTable(new QvxTable()
{
TableName = "Error"
}));
}
}
private QvDataContractResponse GetDatabases(ConnectorParameter parameter)
{
var databaseList = new List <QlikView.Qvx.QvxLibrary.Database>();
try
{
var qlikApp = AppInstance.GetQlikInstance(parameter);
if (qlikApp == null)
{
return new QvDataContractDatabaseListResponse {
qDatabases = databaseList.ToArray()
}
}
;
var apps = qlikApp.GetAllApps();
foreach (var app in apps)
{
databaseList.Add(new QlikView.Qvx.QvxLibrary.Database()
{
qName = app
});
}
return(new QvDataContractDatabaseListResponse {
qDatabases = databaseList.ToArray()
});
}
catch (Exception ex)
{
logger.Error(ex, $"databases not loaded.");
return(new QvDataContractDatabaseListResponse {
qDatabases = databaseList.ToArray()
});
}
}
private QvxTable GetData(ScriptCode script, ConnectorParameter parameter)
{
try
{
var qlikApp = AppInstance.GetQlikInstance(parameter, script.AppId);
if (qlikApp == null)
{
return(new QvxTable());
}
foreach (var filter in script.Filter)
{
logger.Debug($"Filter: {filter}");
foreach (var value in filter.Values)
{
logger.Debug($"");
var result = qlikApp.FirstSession.Selections.SelectValue(filter.Name, value);
if (result == false)
{
logger.Error($"The Dimension \"{filter.Name}\" could not found.");
return(null);
}
}
}
var resultTable = tableFunctions.GetTableInfosFromApp($"Table_{script.AppId}_{script.ObjectId}", script, parameter, qlikApp);
return(resultTable.QvxTable);
}
catch (Exception ex)
{
logger.Error(ex, "The table script can not be executed.");
return(new QvxTable());
}
}
private QvDataContractResponse GetFields(ConnectorParameter parameter, string appId, string objectId)
{
try
{
var oId = GetObjectId(objectId);
if (String.IsNullOrEmpty(oId))
{
throw new Exception("no object id for field table found.");
}
var script = ScriptCode.Create(appId, oId);
var resultTable = tableFunctions.GetTableInfosFromApp("FieldTable", script, parameter);
if (resultTable == null)
{
throw new Exception("no field table found.");
}
return(new QvDataContractFieldListResponse {
qFields = resultTable.QvxTable.Fields
});
}
catch (Exception ex)
{
logger.Error(ex, $"fields from app {appId} and table {objectId} not loaded.");
return(new QvDataContractFieldListResponse {
qFields = new QvxField[0]
});
}
}
private QvDataContractResponse GetTables(ConnectorParameter parameter, string appId)
{
var tables = new List <QvxTable>();
try
{
var qlikApp = AppInstance.GetQlikInstance(parameter, appId);
if (qlikApp == null)
{
return new QvDataContractTableListResponse {
qTables = tables
}
}
;
var options = new NxGetObjectOptions()
{
Types = new List <string> {
"table"
}
};
var appObjects = qlikApp.FirstSession.CurrentApp.GetObjectsAsync(options).Result;
foreach (var obj in appObjects)
{
var table = obj.AsObject <Table>();
tables.Add(new QvxTable()
{
TableName = $"{table.Title} [{table.Id}]"
});
}
var masterObjectList = qlikApp.FirstSession.CurrentApp.GetMasterObjectListAsync().Result;
var listLayout = masterObjectList?.GetLayout()?.As <MasterObjectListLayout>();
if (listLayout != null)
{
foreach (var item in listLayout.AppObjectList.Items)
{
if (item.Data.Visualization == "table")
{
tables.Add(new QvxTable()
{
TableName = $"{item.Data.Title} [{item.Info.Id}]"
});
}
}
}
return(new QvDataContractTableListResponse {
qTables = tables
});
}
catch (Exception ex)
{
logger.Error(ex, $"tables form app {appId} not loaded.");
return(new QvDataContractTableListResponse {
qTables = tables
});
}
}
public override string HandleJsonRequest(string method, string[] userParameters, QvxConnection connection)
{
try
{
QvDataContractResponse response;
AppInstance.LoadMemory();
var parameter = ConnectorParameter.Create(connection?.MParameters);
logger.Trace($"HandleJsonRequest {method}");
switch (method)
{
case "getVersion":
response = new Info {
qMessage = GitVersionInformation.InformationalVersion
};
break;
case "getUsername":
response = new Info {
qMessage = parameter.UserName
};
break;
case "getDatabases":
response = GetDatabases(parameter);
break;
case "getTables":
response = GetTables(parameter, userParameters[0]);
break;
case "getFields":
response = GetFields(parameter, userParameters[0], userParameters[1]);
break;
case "getPreview":
response = GetPreview(parameter, userParameters[0], userParameters[1]);
break;
default:
response = new Info {
qMessage = "Unknown command"
};
break;
}
AppInstance.SaveMemory();
AppInstance.Dispose();
return(ToJson(response));
}
catch (Exception ex)
{
logger.Error(ex);
return(ToJson(new Info {
qMessage = "Error " + ex.ToString()
}));
}
}
/// <summary>
/// 创建一个Socket连接，连接远程地址address，address的格式为“[IP地址或者Host地址]:[端口]”
/// </summary>
/// <param name="address"></param>
public Connector MakeConnection(string address, EProtocolType protocol, IDataHandler dataHandler, IEventHandler eventHandler)
{
if (connectorTable == null)
{
return(null);
}
if (string.IsNullOrEmpty(address))
{
return(null);
}
string[] tmp = address.Split(':');
if (tmp.Length != 2)
{
return(null);
}
if (string.IsNullOrEmpty(tmp[0]))
{
return(null);
}
Connector connector = new Connector();
connector.ProtocolType = protocol;
connector.EventHandler = this;
connector.DataHandler = this;
ConnectorParameter parameter = new ConnectorParameter {
connector = connector, dataHandler = dataHandler, eventHandler = eventHandler
};
connectorTable[connector] = parameter;
IPAddress ipAddress = null;
if (!IPAddress.TryParse(tmp[0], out ipAddress))
{
connector.Host = address;
}
else
{
connector.IP = address;
}
connector.Start();
return(connector);
}
private QvDataContractResponse GetPreview(ConnectorParameter parameter, string appId, string objectId)
{
try
{
var oId = GetObjectId(objectId);
if (String.IsNullOrEmpty(oId))
{
throw new Exception("no object id for preview table found.");
}
var script = ScriptCode.Create(appId, oId);
var resultTable = tableFunctions.GetTableInfosFromApp("PreviewTable", script, parameter);
if (resultTable == null)
{
throw new Exception("no preview table found.");
}
return(resultTable.Preview);
}
catch (Exception ex)
{
logger.Error(ex, $"fields from app {appId} and table {objectId} not loaded.");
return(new TableFunc.PreviewResponse());
}
}
public TableHelper GetTableInfosFromApp(string tableName, ScriptCode script, ConnectorParameter parameter, QlikApp qlikApp = null)
{
try
{
var resultTable = new QvxTable()
{
TableName = tableName,
};
HyperCubePager pager = null;
IEnumerable <int> columnOrder = null;
var fields = new List <QvxField>();
var rows = new List <QvxDataRow>();
var size = new Size();
if (qlikApp == null)
{
qlikApp = AppInstance.GetQlikInstance(parameter, script.AppId);
}
if (qlikApp == null)
{
return(null);
}
var masterObject = qlikApp.FirstSession.CurrentApp.GetMasterObjectAsync(script.ObjectId).Result;
if (masterObject != null)
{
logger.Debug($"find master object: {script.ObjectId}");
var genericObject = qlikApp.FirstSession.CurrentApp.CreateGenericSessionObjectAsync(masterObject.Properties).Result;
pager = genericObject.GetAllHyperCubePagers().FirstOrDefault() ?? null;
var tableLayout = genericObject.GetLayout().As <TableLayout>();
var hyperCube = tableLayout.HyperCube;
columnOrder = hyperCube.ColumnOrder;
size = hyperCube.Size;
fields.AddRange(GetHyperCubeFields(hyperCube.DimensionInfo, hyperCube.MeasureInfo, script));
}
var table = qlikApp.FirstSession.CurrentApp.GetObjectAsync <Table>(script.ObjectId).Result;
if (table != null)
{
logger.Debug($"table object: {script.ObjectId}");
pager = table.HyperCubePager;
columnOrder = table.ColumnOrder;
size = table.Size;
fields.AddRange(GetHyperCubeFields(table.DimensionInfo, table.MeasureInfo, script));
}
var preview = new PreviewResponse()
{
MaxCount = 15,
};
if (script != null)
{
var initalPage = new NxPage {
Top = 0, Left = 0, Width = size.cx, Height = preview.MaxCount
};
var allPages = new List <IEnumerable <NxDataPage> >();
allPages.Add(pager.GetData(new List <NxPage>()
{
initalPage
}));
if (script.Full)
{
var pageHeight = Math.Min(size.cy * size.cx, 5000) / size.cx;
logger.Debug($"read data - column count: {size.cx}");
initalPage = new NxPage {
Top = 0, Left = 0, Width = size.cx, Height = pageHeight
};
allPages = pager.IteratePages(new[] { initalPage }, Pager.Next).ToList();
preview.MaxCount = 0;
}
if (allPages == null)
{
throw new Exception($"no dimension in table {script.ObjectId} exits.");
}
logger.Debug($"read pages - count {allPages.Count}");
foreach (var page in allPages)
{
var allMatrix = page?.SelectMany(p => p.Matrix);
foreach (var matrix in allMatrix)
{
var row = new QvxDataRow();
var hrow = new PreviewRow();
var drow = new PreviewRow();
foreach (var order in columnOrder)
{
if (order < fields.Count)
{
var field = fields[order];
row[field] = matrix[order].Text;
if (!preview.qPreview.Any(s => s.qValues.Contains(field.FieldName)))
{
hrow.qValues.Add(field.FieldName);
}
if (preview.qPreview.Count <= preview.MaxCount)
{
drow.qValues.Add(matrix[order].Text);
}
}
}
rows.Add(row);
if (hrow.qValues.Count > 0)
{
preview.qPreview.Add(hrow);
}
if (drow.qValues.Count > 0)
{
preview.qPreview.Add(drow);
}
}
}
}
resultTable.Fields = fields.ToArray();
resultTable.GetRows = () => { return(rows); };
logger.Debug($"return table {resultTable.TableName}");
return(new TableHelper(resultTable, preview));
}
catch (Exception ex)
{
logger.Error(ex, "can´t read table infos.");
return(null);
}
}
private static void Menu_NewSite_Click(object sender, EventArgs e)
{
lock (NewSiteActiveLock)
{
if (NewSiteActive)
{
return; //Only one new site dialog at a time
}
}
//No repository found, prompt the user to create one
CreateRepository CR = new CreateRepository(_Connectors, ListOfRepositoryUI);
lock (NewSiteActiveLock)
{
NewSiteActive = true;
}
DialogResult DR = CR.ShowDialog();
lock (NewSiteActiveLock)
{
NewSiteActive = false;
}
if (DR == DialogResult.OK)
{
Int32 CreatedRepositoryId;
SQLiteHelper oSQLHelper = new SQLiteHelper(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.FullName + "." + System.Reflection.MethodBase.GetCurrentMethod().Name, null);
if (!oSQLHelper.InitConnection())
{
return;
}
ConnectorParameter CP = new ConnectorParameter()
{
Server = CR.ResultSiteURL, Login = CR.ResultLogin, Password = CR.ResultPassword, SitePath = String.Format("/Sites/{0}/documentLibrary", CR.ResultSiteName), EnableFullScan = 0
};
String ConnectorParameters = ConnectorParameter.Serialize(CP);
oSQLHelper.SetCommandText("INSERT INTO Repository (Name,LocalRepository,RemoteRepositoryConnector,RemoteRepositoryParameters) VALUES('" + CR.ResultSiteName + "','" + CR.ResultLocalPath + "','" + CR.ResultSelectedType + "',@ConnectionString)");
oSQLHelper.SetCommandParameter("@ConnectionString", DbType.String, 500, ConnectorParameters);
if (!oSQLHelper.ExecuteNonQuery())
{
return;
}
object QueryResult = null;
oSQLHelper.SetCommandText(@"SELECT MAX(Id_Repository) FROM Repository");
if (!oSQLHelper.ExecuteScalar(ref QueryResult))
{
oSQLHelper.Dispose(); return;
}
CreatedRepositoryId = Convert.ToInt32(QueryResult);
oSQLHelper.Dispose();
if (!InitRepository(CreatedRepositoryId, true))
{
logger.Debug("In Menu_NewSite_Click - Unable to init repository");
return;
}
//Refresh the list of sites in the menu
Menu_Sites.DropDownItems.Clear();
foreach (RepositoryUI TempRUI in ListOfRepositoryUI)
{
Menu_Sites.DropDownItems.Add(TempRUI.MenuItem_Main);
}
Menu_Sites.Enabled = true;
}
}
